| |
 |
|
Oracle10g
SQL*Trace Changes
Oracle Tips by Burleson Consulting |
Oracle provides many ways to collect a SQL trace
dump, including the popular dbms_session.set_sql_trace and the
dbms_session.session_trace_enable procedures.
Dr. Hall lists all permutations of the SQL trace utilities:
-- All versions.
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,
level 8';
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123,
serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8,
nm=>' ');
-- Available from SQL*Plus since 8i (command line utility prior to
this.
SQL> CONN sys/password AS SYSDBA; -- User must have SYSDBA.
SQL> ORADEBUG SETMYPID; -- Debug current session.
SQL> ORADEBUG SETOSPID 1234; -- Debug session with the specified OS
process.
SQL> ORADEBUG SETORAPID 123456; -- Debug session with the specified
Oracle process ID.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME; -- Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;
-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
In Oracle 10g, the dbms_monitor
package is the tool of choice for SQL trace file generation.
-- Oracle 10g
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE,
binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234,
serial_num=>1234);
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g',
module_name=>'test_api', action_name=>'running');
|
