SQL from The TRACEFILE
OS: IBM AIX5.1
DB: Oracle 9i R2
I just started the Databaes trace with following steps:
Modify the "init.ora" file,stop/start the instance to make the parameter effective.
SQL> connect internal
SQL> shutdown immediate
SQL> audit select any table;
SQL> audit drop any table;
Now how can I see the SQL Statements Users are submitting against the tables.
Thanks for all the replies.
But watch out it would effect the performance big time!
Life is a journey, not a destination!
I wrote the below summary from Ton Kyte's book, for some co-workers. This should help:
Turning on SQL_TRACE for the entire instance is possible, via:
alter system set SQL_TRACE = TRUE
but... we would never do that in a production database, nor in any database for a long period of time. It generates tons of output, and is a huge load on the instance.
Interactively, you can turn it on for your currently connected session (such as in SQLPLUS) via:
alter session set SQL_TRACE = TRUE
This is only useful when you want to trace the CURRENT session you are connected to, so it does not help when there is some other (for example, application) session that you want session data for. But that can be done using this:
where the 107 and the 16995 are examples of the SID and the SERIAL# columns, respectively, of the v$session view that you want to trace.
If you don't know when the session is going to connect and do its thing that you want to trace, then write a logon trigger. What that would do is intercept the logon event, and turn on tracing, and then let the session proceed as normal.
As with any of these trace files that get generated, the output ends up in the directory spec'ed by this:
select value from v$parameter where name = 'user_dump_dest'
Once the trace file is generated, you use the "tkprof" utility to format it nicely, thus:
/db/app/oracle/admin/webdev/udump$ tkprof webdev_ora_15672.trc report.txt
And then you look at "report.txt" and it looks pretty, and contains all the SQL that is being done by that session.
Click Here to Expand Forum to Full Width