SQL from The TRACEFILE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL from The TRACEFILE

  1. #1
    Join Date
    Jan 2003
    Posts
    33

    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    But watch out it would effect the performance big time!

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jul 2003
    Posts
    59
    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.
    Tom Best

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width