# | Command | Comments |
1 | ALTER SESSION SET SQL_TRACE = TRUE ;
/* execute your code */
ALTER SESSION SET SQL_TRACE = FALSE ;
COMMIT; | -- To Turn on tracing
-- To Turn off tracing
In some Oracle release, ROW SOURCE info
will not be generated. commit/rollback will
ensure that STAT Lines get written in the
trace data. |
2 | Alter session set events ‘10046 trace name context forever, level x’ ;
/* Execute your code */
Alter session set events ‘10046 trace name context off’ ; | Where X can have the following values:
0 – Tracing is disabled
1 – Default level; Equivalent to SQL_TRACE
= true
4 – Provides BIND variables’ values
8 - Provides WAIT events
12 – Provides both BIND variables’ values
and WAIT events
To Turn off tracing |
3 | In init.ora file:
EVENT = ‘10046 trace name context forever, level 8’; | At instance level, enables the trace event. |
4 | DBMS_SUPPORT package to trace your session.
SQL> exec sys.dbms_support.start_trace ;
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
5
SQL> exec sys.dbms_support.stop_trace ;
PL/SQL procedure successfully completed. | Enabling WAIT events, and BIND Variables
using dbms_support.
SQL> exec
sys.dbms_support.start_trace(waits =>true,
binds=> true); |
5 | How to trace someone else’s session
SQL> exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION
(16, 181, 'sql_trace', TRUE);
SQL> exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION
(16, 181, 'sql_trace', FALSE); | Where 16 is SID and 181 is Serial# obtained
from V$SESSION.
To turn on tracing
To Turn off tracing |
6 | SQL> exec
dbms_system.set_sql_trace_in_session(448,2288,TRUE);
SQL> exec
dbms_system.set_sql_trace_in_session(448,2288,FALSE); | Turn SQL tracing on in session 448 (SID)
and 2288 (Serial#)
Turn SQL tracing off in session 448 |
7 | SQL> exec dbms_system.set_ev(8, 1158, 10046, 12, '');
Where 8 is SID, and 1158 is the Serial#.
SQL> exec dbms_system.set_ev(8, 1158, 10046, 0, '');
Will stop tracing | Set event 10046 (SQL_TRACE) at level 12 to
collect information about all wait events and
bind variables. The trace information will get
written to user_dump_dest. |
8 | Using DBMS_MONITOR Package
exec sys.dbms_monitor.session_trace_enable(
session_id=>100,serial_num=>482, waits=>true, binds=>true);
/* Execute your SQL script */
exec sys.dbms_monitor.session_trace_disable(
session_id=>100,serial_num=>482); | Start tracing the session 100, serial# 482
Stop tracing |
9 | Using oradebug
SQL> select p.spid, s.paddr, s.sid, s.serial#, p.addr
from v$session s, v$process p
where s.username = 'TAMIL' and s.paddr = p.addr ;
SPID PADDR SID SERIAL# ADDR
------------ ---------------- ---------- ---------- ----------------
1369 0000000398F69D40 95 4 0000000398F69D40
SQL> oradebug setospid 1369
Oracle pid: 20, Unix process pid: 1369, image: oracle@hostname
(TNS V1-V3)
SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.
SQL> oradebug event 10046 trace name context off ;
Statement processed. |
You need to login as SYS to execute
oradebug command.
In another user session, execute the SQL
script
|
10 | Using LOGON Trigger
create or replace trigger user_logon_trg
after logon on database
begin
if USER = 'TAMIL' then
execute immediate
'alter session set events ''10046 trace name context forever, level 8''';
end if;
end;
/
LOGOFF Trigger
create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = 'TAMIL' then
execute immediate
'alter session set events ''10046 trace name context off';
end if;
end;
/ |
This is my favorite method to generate trace
data on some one else’s code.
Always use “IF USER “ check, otherwise, the
file system will be run out of space.
|
11 | Using DBMS_MONITOR
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
service_name IN VARCHAR2,
module_name IN VARCHAR2 DEFAULT ANY_MODULE,
action_name IN VARCHAR2 DEFAULT ANY_ACTION,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL);
DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(
service_name IN VARCHAR2,
module_name IN VARCHAR2,
action_name IN VARCHAR2 DEFAULT ALL_ACTIONS,
instance_name IN VARCHAR2 DEFAULT NULL);
You can also set module name, action name using
DBMS_APPLICATION_INFO package within your application code.
Example:
SQL> select username, service_name, module, action , sid
from V$SESSION where username='TAMIL';
USERNAME SERVICE_NAME MODULE ACTION SID
---------------- ----------- ------------ -------- ----------
TAMIL ORDBA SQL*Plus 107
SQL> begin
dbms_monitor.serv_mod_act_trace_enable
('ORDBA', 'SQL*Plus',
DBMS_MONITOR.ALL_ACTIONS, TRUE, TRUE, NULL);
end;
/
PL/SQL procedure successfully completed.
SQL> select /* test dbms monitor */ count(1) from emp ;
COUNT(1)
----------
5
To disable tracing,
use dbms_monitor.serv_mod_act_trace_disable procedure. | New feature in 10g for tracing a user session. |
No comments:
Post a Comment