Enable before start session trace in oracle
=============================================
ALTER SESSION SET TRACEFILE_IDENTIFIER = "USER_TRACE" ';
alter session set timed_statistics = true';
alter session set statistics_level=ALL';
alter session set max_dump_file_size=UNLIMITED';
Enable trace for user
=================================
CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'HR'
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "USER_TRACE" ';
EXECUTE IMMEDIATE 'alter session set timed_statistics = true';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
* Drop trigger for disable
Enable trace for session
=======================================
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Disabling trace
==============================================
alter system set events '10046 trace name context off';
Find its OS pid from the following
================================================
select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID;
SQL> select sid,serial# from v$session where username='HR';
SID SERIAL#
---------- ----------
42 293
Trace session useing pl/SQL package(DBMS_MONITOR)
==================================================================
Enable tracing
------------------------------------
EXEC DBMS_MONITOR.session_trace_enable(session_id =>42, serial_num=>293, waits=>TRUE, binds=>TRUE);
Disable Tracing
-------------------------------
EXEC DBMS_MONITOR.session_trace_disable(session_id=>42, serial_num=>293);
Identify trace file using SID
==========================================
SELECT p.tracefile
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = 42;
Example
----------------
SQL> SELECT p.tracefile
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = 42; 2 3 4
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/hr/hr/trace/hr_ora_10369.trc
SQL>
check own SID and trace file
=================================
SET LINESIZE 100
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
=====================================================================
Oracle provides event 10046 that can also aid us in collecting extended SQL trace data.
There are four levels available when setting up a trace with Event 10046:
Level 1: this cause tracing of sql activities and is similar to 'alter session set sql_trace=true'
Level 4: provides level 1 tracing + displays the values for all bind variables. It is equivalent to dbms_support.start_trace(waits=>false,binds=>true);
Level 8: provides level 1 tracing and displays a list of all database wait events. It is equivalent to dbms_support.start_trace(waits=>true,binds=>false);
Level 12 provides level 1 tracing in addition to both bind variable substitution and database wait events. It is equivalent to dbms_support.start_trace(waits=>true,binds=>true);
Note that Level 0 disables tracing.
1 is same as a regular trace.
4 means also dump bind variables
8 means also dump wait information
12 means dump both bind and wait information
Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above) (Doc ID 293661.1)
#########################################
Fron oracle Support
##########################################
DBMS_MONITOR.session_trace_enable in SQL*Plus Current Session or Profile Option Method
It is not recommended to use DBMS_MONITOR.session_trace_enable for tracing the current session in SQL*Plus or in the Profile Option “Initialization SQL Statement - Custom”.
The APPS user is used in both of these scenarios and it may not have the correct privilege to run DBMS_MONITOR.
2.19 Obtaining trace for another database session prior to 10g
Prior to 10g the DBMS_MONITOR package is not available. In this case the DBMS_SYSTEM.set_sql_trace_in_session or
DBMS_SUPPORT.start_trace_in_session procedures can be used to trace another session.
The DBMS_SUPPORT and DBMS_SYSTEM packages are not documented So, they should be avoided by customers, unless specifically
instructed by either Oracle Support or Oracle Development.
The DBMS_SUPPORT package is not installed by default. The package is supplied in files dbmssupp.sql and prvtsupp.plb (these are in $ORACLE_HOME/rdbms/admin).
The DBMS_SYSTEM package is usually already installed (but it can be installed using catproc.sql).
Public synonyms are not created (by default) for either package and no privileges are granted. So use SYS to run these procedures.
See My Oracle Support documents:
The DBMS_SUPPORT Package (Document 62294.1)
For both of these methods the Session ID (SID) and Serial Number will be needed. These can be obtained by querying the v$session and v$process views.
DBMS_SYSTEM
To enable SQL Trace run the following:
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=><sid>,
serial#=><serial#>,
sql_trace=>TRUE);
To disable SQL Trace run the following:
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=><sid>,
serial#=><serial#>,
sql_trace=>FALSE);
DBMS_SUPPORT
To enable SQL Trace run the following :
EXEC DBMS_SUPPORT.start_trace_in_session(sid=><sid>,
serial=><serial#>,
waits=>true,
binds=>false);
Note that with waits, but not binds is recommended (and the default in this case). But other options can be chosen.
To disable SQL Trace run the following :
EXEC DBMS_SUPPORT.stop_trace_in_session(sid=><sid>,
serial=><serial#>);
Serial Number can be set to NULL (or 0) to trace the current session with the supplied SID.
SERIAL only needs to be specified to ensure that a specific session is traced.
If the session ends and the SID is reused before the START_TRACE_IN_SESSION call is made then including
the SERIAL will cause an error to be raised (rather than the trace being started against a different session to the one intended).
