Trace all of the sessions from a user in oracle

Oracle provide a functionality to trace session or instance. If we have to trace all the sessions of a user then we can not do this at the moment as there is no functionality for this. Do not get disharted, there is a way to trace all of the user sessions.

First of all lets discuss why we need to trace all sessions of a user. If you are using a third party application and the application creates split second sessions with database then we have no time to get the SID and Serial number to start trace on it even we can not change the call to include the sql to enable trace on the session.

So how we do it.

1. Create a log on trigger for a user. It should call a procedure to start tracing for the current session.

2. Create another procedure to stop tracing from those sessions.

3. Once you want to stop the trigger just drop it.

Below are the script that I have used to simulate this tracing.

-- Create table to hold sid and serial

create table hdhillon.tracet (SID number(8),  serial number(8));

-- Procedure to start trace

CREATE OR REPLACE procedure SYS.startTracing
is
v_sid number;
v_serial number;
cursor c_sid is
select
   sys_context('USERENV','SID')
from dual;
BEGIN
open c_sid;
loop
    fetch c_sid into v_sid;
exit when c_sid%NOTFOUND;
select distinct serial# into v_serial from v$session
where SID = v_sid;
        insert into hdhillon.tracet (SID,Serial) values(v_sid,v_serial);
    dbms_session.set_identifier('harvey_trace');
dbms_monitor.session_trace_enable( v_sid, v_serial, TRUE,TRUE);
    end loop;
close c_sid;
END;
/

-- Logon trigger

CREATE OR REPLACE TRIGGER SYS.Harvey_USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'HDHILLON'
  THEN
    startTracing();
    END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

-- Procedure to stop the tracing

CREATE OR REPLACE procedure SYS.stopTracing
is
v_sid number;
v_serial number;
cursor c_sid is
select
   SID, serial
from hdhillon.tracet;
BEGIN
open c_sid;
loop
    fetch c_sid into v_sid, v_serial;
exit when c_sid%NOTFOUND;
    dbms_monitor.session_trace_disable( v_sid, v_serial);
    delete from hdhillon.tracet where sid = v_sid;
    end loop;
close c_sid;
commit;
END;
/

-- Script to generate human readable trace files

select 'trcsess output=myoutput' || SID || '_' || SERIAL || '.trc session=' || SID || '.' || SERIAL from hdhillon.tracet;

--Clear procedures and triggers

DROP TRIGGER SYS.Harvey_USER_TRACE_TRG;
drop procedure  SYS.startTracing ;
drop procedure SYS.stopTracing;
drop procedure SYS.stopTracingRunning;