70 lines
2.7 KiB
SQL
70 lines
2.7 KiB
SQL
/*
|
|
This script will set the SQL Trace on in passed SID.
|
|
|
|
|
|
CONN sys/password AS SYSDBA; -- User must have SYSDBA.
|
|
|
|
oradebug SETORAPNAME PR00 -- Debug process by name.
|
|
ORADEBUG SETMYPID; -- Debug current session.
|
|
ORADEBUG SETOSPID 1234; -- Debug session with the specified OS process.
|
|
ORADEBUG SETORAPID 123456; -- Debug session with the specified Oracle process ID.
|
|
|
|
ORADEBUG TRACEFILE_NAME; -- Display the current trace file.
|
|
oradebug UNLIMIT -- Set trace to unlimited size.
|
|
|
|
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12; -- Start SQL Trace
|
|
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF; -- Stop SQL Trace
|
|
|
|
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12; -- Start Optimizer Trace
|
|
|
|
exec dbms_monitor.session_trace_enable (session_id=>123,serial_num=>123, waits=> TRUE, binds=> true );
|
|
exec dbms_monitor.session_trace_disable (session_id=>123,serial_num=>123);
|
|
|
|
-- Trace user's own session
|
|
exec dbms_monitor.session_trace_enable (NULL,NULL,TRUE,TRUE) ;
|
|
exec dbms_monitor.session_trace_disable ;
|
|
|
|
alter system set events '1031 trace name errorstack level 3';
|
|
|
|
*/
|
|
|
|
BEGIN
|
|
FOR i IN ( SELECT SID, serial#
|
|
FROM v$session
|
|
WHERE SID = &&1 )
|
|
LOOP
|
|
/* Set session DUMP file size */
|
|
SYS.dbms_system.set_int_param_in_session ( i.SID
|
|
, i.serial#
|
|
, 'MAX_DUMP_FILE_SIZE'
|
|
, 2097152 -- operating system blocks
|
|
);
|
|
/* Set session TIMED_STATISTICS to TRUE */
|
|
SYS.dbms_system.set_bool_param_in_session ( i.SID
|
|
, i.serial#
|
|
, 'TIMED_STATISTICS'
|
|
, TRUE
|
|
);
|
|
|
|
-- alter session set events '10046 trace name context forever, level 12';
|
|
/* Set session trace level to 12 */
|
|
dbms_system.set_ev ( i.SID
|
|
, i.serial#
|
|
, 10046
|
|
, 12
|
|
, ''
|
|
);
|
|
|
|
/* Set session SQL trace to TRUE */
|
|
dbms_system.set_sql_trace_in_session ( i.SID
|
|
, i.serial#
|
|
, TRUE
|
|
);
|
|
|
|
/* To disable trace*/
|
|
-- dbms_system.SET_BOOL_PARAM_IN_SESSION(i.SID,i.serial#,'SQL_TRACE',FALSE);
|
|
|
|
END LOOP;
|
|
END;
|
|
/
|