Tracing the Oracle database

0
Tracing the Oracle database:-

First open the sql developer and login as a user with administrator previlage. eg:-system


1)  Enable Oracle database to gather statistics (on session or system level)

ALTER SYSTEM SET  timed_statistics = true; 
ALTER SESSION SET  timed_statistics = true;


2) Find the SID, SERIAL# for a specific session you want to monitor  

SELECT username, sid, serial#, program FROM v$session WHERE username = <User_Name>; 


3)Enable the tracing for this session (  must be logged as SYSDBA ) 

EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, true); 

Bind Variables with Value:

EXECUTE dbms_monitor.session_trace_enable(41, 2216, waits=>true, binds=>true) ;

 Query to Generate trace command based on the current session ids:

  SELECT p.tracefile,s.username, s.SID, s.serial#, s.PROGRAM, p.spid ,
'EXECUTE dbms_monitor.session_trace_enable('|| s.SID||','||s.serial#||', waits=>true, binds=>true) ;' ENAB,
'EXECUTE dbms_monitor.session_trace_enable('|| s.SID||','||s.serial#||', waits=>false, binds=>false) ;' DISAB
FROM   v$session s,

              v$process p
WHERE p.addr = s.paddr   and s.username = 'SYS'    and s.Program not like '%SQL%'
and SID<>274

order by sid 


4)Identify the directory where the trace file is generated 

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

5) Identify the name of the trace file which is generated 

  

SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid 
FROM   v$session s,  

              v$process p 
WHERE p.addr = s.paddr and s.username = <User_Name>; 

  

The name of the trace file is  <oracle_sid>_ora_<p.spid>.trc 


6) Disable the tracing for this session (  must be logged as SYSDBA ) 

EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);