Enable the SQL trace at session level in Oracle

Leave a reply

Enable or disable the SQL trace at session level in Oracle

Oracle offers the DBMS_MONITOR package for tracing SQL at the session level. This helps identify issues with specific SQL queries and shows which session is executing them.

Enable and disable trace for SQL Query running from particular session or user

1. Determine the SID and serial# from following query by username/query.

-- From User
SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'OE';

-- From SQL Query
set pagesize 100 linesize 132
col username format a10
col sql_text format a60
SELECT USERname, terminal, SID, a.module, SERIAL#, SQL_TEXT
FROM V$SESSION a, V$SQL b
WHERE a.SQL_ADDRESS = b.ADDRESS AND upper(SQL_TEXT) like '%SELECT * from HR.EMP%' AND username IS NOT NULL;

2. Execute the following command to enable the trace for particular SID and serial# number.

First Way:

For Enable:

exec DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27,serial_num => 60 , waits => true , binds => false);

For Disable:

exec DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id => 27,serial_num => 60);

Note:
waits: true means include in trace file
binds: true means include in trace file

Second Way:

For Enable:

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);

For Disable:

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

Checked the Enabled Traces in Database

DBA_ENABLED_TRACES view help to know about the trace is still enabled or disabled. No row means all trace is disable in database. It is necessary to check because some session may generate lot of log files and may cause space crunch.

Verify the trace is enabled or disable:

SELECT * FROM DBA_ENABLED_TRACES;

SELECT * FROM DBA_ENABLED_AGGREGATIONS ;

Find the user is login in particular time and enable trace for that session

Following queries help us to find out the username “VPPILOT” connected to the database with login_time then we fetch the session id and and enable trace for particular session in between that time period.

Oracle 11g:
SELECT 'exec dbms_monitor.session_trace_enable(session_id=>' ||to_char(sid) ||',serial_num=>'||to_char(serial#)||',binds=>true,waits=>true) /* ' || program || ' - ' || username || ' - ' || to_char(logon_time, 'DD-MM-YYYY HH24:MI' ) ||' */;'
FROM v$session where (lower(program) like 'staff%' or lower(program) like 'vagtplan%' or lower(program) like 'vaktplan%')
and trunc(logon_time) = trunc(sysdate) and to_char(logon_time, 'HH24:MI') between '2000' and '2030' and username = 'VPPILOT';

Oracle 10g
SELECT 'execute dbms_system.set_sql_trace_in_session(' ||to_char(sid) ||','||to_char(serial#)||', true) /* ' || program || ' - ' || username || ' - ' || to_char(logon_time, 'DD-MM-YYYY HH24:MI' ) ||' */;'
FROM v$session where (lower(program) like 'staff%' or lower(program) like 'vagtplan%' or lower(program) like 'vaktplan%')
and trunc(logon_time) = trunc(sysdate) and to_char(logon_time, 'HH24:MI') between '2000' and '2030' and username = 'VPPILOT';



Leave a Reply