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> @$ORACLE_HOME/admin/cataudit.sql
SQL> shutdown immediate
SQL> startup

SQL> audit select any table;
SQL> audit drop any table;
SQL> audit......
many more..

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!

Sam

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.

Alternatively...
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:

begin
sys.dbms_system.set_sql_trace_in_session(107,16995,TRUE);
end;

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.