This content has been marked as final. Show 8 replies
876149 wrote:ALTER SESSION SET SQL_TRACE=TRUE;
My application is running on ASP.Net (Frontend) and Oracle DB (Backend).
When I run one report from Frontend, I want to know which store procedure or query is
fired/executed from backend, or want to know last executed sql query from backend.
Status Level: Newbie
Registered: Jul 30, 2011
Total Posts: 33
Total Questions: 14 (10 unresolved)
I extend my condolences to you since you rarely get answers to your questions here.
As sb92075 suggested, you could enable SQL_TRACE. But this implies that if you haven't enabled SQL_TRACE before running your query, you could not use trace file (genereated by Oracle when SQL_TRACE has been set to TRUE).
So, if you can't run again your query, you could try to look at V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY, if you have access to these views. So before continuing in the suggestion of a solution to your concern, I would ask you if you have access to these views.
like others already said, the only reliable way to capture all SQL from a certain user/session/module is SQL tracing, which has such serious drawbacks as very significant space consumption (in a few hours, your trace file can eat up several gigabytes of sace) and some performance overhead. Besides, you need access to the database server filesystem to retrieve the trace file, and you'll need to come up with some text parsing tools if you want automate trace file processing.
It's much easier to retrieve this information directly from the database, e.g. from V$SQL, but here the problem is that retention time for a query in V$SQL is not guaranteed, and if the shared pool is sufficiently busy, the statement can get aged out seconds after the exectuion. If you have the Diagnostic Pack license, you can use AWR views for this purpose (DBA_HIST_SQLSTAT etc.) or ASH (V$ACTIVE_SESSION_HISTORY/DBA_HIST_ACTIVE_SESS_HISTORY), however, here the chances of missing a statement are even higher (becase only top N heaviest statements are captured, where N is given by AWR retention settings).
So as you can see, this is not a simple task. If you think about it, it should be easy to understand why: Oracle databases are made to process thousands of SQL queries per second, and a large percentage of these queries is system-generated, so a situation when every single SQL would have to be captured are rare and unusual.
In your case, I think if you explained us in a bit more detail what exactly you are trying to achieve, we could have come up with a better solution for you. Most likely what you need is proper code instrumentation (i.e. an application log table and some database code to populate it).