Trace SQL at session level in Oracle

SQL trace at session level in Oracle

Oracle provide packages DBMS_MONITOR for tracing the SQL at session level. Tracing SQL queries will help to find out issue going with particular SQL and which session is running which SQL queries.

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 disable.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';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.