SQL TRACE

SQL TRACE


exec sys.dbms_system.set_sql_trace_in_session(144,3227,TRUE);

EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( &sid, &serial#, true );
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( &sid, &serial#, false );
EXEC DBMS_SYSTEM.set_ev(si=>144, se=>3227, ev=>10046, le=>12, nm=>'');
EXEC DBMS_SYSTEM.set_ev(si=>144, se=>3227, ev=>10046, le=>0, nm=>'');//to disable


grep for the following pattern in the user dump destination(user_dump_dest)

grep sid.serial# *.trc

tkprof .trc .prf sort=fchela sys=no

After entering the menu option HCLM, at sql prompt please type the below query,

select sid,serial# from v$session where upper(client_info) like '%CLM%'.

This will return the sid and serial# of the session of HCLM and for which trace needs to be enabled as below.

exec dbms_system.set_ev(, , 10046, 12, NULL);