1. alter session set events ¡®10046 trace name context forever,level 12¡Ç;
alter session set tracefile_identifier=¡¯10046¡Ç; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited;
Start trace:
alter session set events ¡®10046 trace name context forever,level 12¡Ç;
Stop trace:
alter session set events ¡®10046 trace name context off¡¯; Note: level can be 4,8,12, only trace your own session 2. Using package DBMS_MONITOR EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,waits => TRUE, binds => TRUE);
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60); Trace a module: EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE( service_name=>'vasont.world', module_name=>'VasontU.exe', action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE, binds=>TRUE,instance_name=>NULL);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE( service_name=>'vasont.world',module_name=>'VasontU.exe'); Note: not available before 10g, can trace any session
3. Using package DBMS_SESSION EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE(); Note: only trace your own session 4. Using package DBMS_SYSTEM execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507, serial#=>4957,sql_trace=>TRUE);
execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>507, serial#=>4957,sql_trace=>FALSE); Note: available in 8i/9i/10g, can trace any session, package is wrapped.
5. oradebug Trace session sid=58;select p.PID,p.SPID from v$process p,v$session s where s.paddr = p.addr and s.sid = 58; PID SPID ———- ——— 32 12943 connect / as sysdba oradebug setospid 12943 //or oradebug setorapid 32 oradebug unlimit oradebug event 10046 trace name context forever,level 12 Stop trace:
oradebug event 10046 trace name context off
Note: available in 8i/9i/10g, can trace any session 6. Using DBMS_SUPPORT
exec DBMS_SUPPORT.START_TRACE_IN_SESSION(&SID, waits=>true, binds=>true );
exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null );
NOTE: need to install before use SQL> connect / AS SYSDBA
SQL> @?rdbmsadmindbmssupp.sql
SQL> GRANT execute ON dbms_support TO schema_owner;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; 7. Using trcsess
trcsess [output=output_file_name]
[session=session_id]
[clientid=client_id]
[service=service_name]
[action=action_name]
[module=module_name]
[trace_files] Trace a module:
trcsess service=vasont.world module=VasontU.exe trc.log
8. Tracing whole system alter system set events '10046 trace name context forever,level 12';
or
event="10046 trace name context forever,level 12" Stop system wider trace: alter system set events '10046 trace name context off'; 9. Using trigger to start traces There may be some situations where it is necessary to trace the activity of a specific user. In this case a logon trigger could be used.
An example is provided below: CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/ Last step, after trace processing, using tkprof tkprof vasont_ora_22103.trc vasont_ora_22103.trc.log sys=no waits=yes sort=(prscnt, execnt) |