It is important to know how to capture trace information at various levels and this section, combined with the notes in the Appendix will cover the main requirements.
Tracing can be captured at a session or system level. The output is generally generated as a “*.trc” file in the user_dump_dest area.
Timed_statistics must be set to TRUE, either at the Server or session level
ALTER SYSTEM SET TIMED_STATISTICS=TRUE;
ALTER SYSTEM SET TIMED_STATISTICS=FALSE;
From within a sqlplus session tracing can be activated by :
ALTER SESSION SET SQL_TRACE TRUE;
From within a PL/SQL block
DBMS_SESSION.SET_SQL_TRACE(TRUE);
To trace a form
Use statistics=yes on the command line
An example is
F45runx decm101s username/password statistics=yes
To set tracing on another session (as user SYS)
execute dbms_system.set_sql_trace_in_session(sid,serial#,TRUE|FALSE);
An piece of PL/SQL code that enables tracing for all sessions belonging to a specific username :
begin
For
sess_rec in (select sid,serial# from v$session where username like upper (‘&user_name’))
loop
sys.dbms_system.set_sql_trace_in_session
(sess_rec.sid,sess_rec.serial#,TRUE);
end loop;
end;
To set tracing for the whole instance,alter the init.ora parameter sql_trace = true and then restart the instance
Level 1 produces standard SQL Trace.
Level 4 reports Bind Variables values on raw SQL Trace.
Level 8 reports database and idle Waits on raw SQL Trace.
Level 12 reports both Bind Variables and Waits on SQL Trace file.
Note any value above 12 is accepted but is treated as a level 12 trace
It can be set in several ways.
On your own session
alter session set events '10046 trace
name context forever, level 12';
On all sessions by adding the init.ora parameter and restarting the database. This appears to be the same as adding SQL_TRACE = TRUE to the init.ora file
event="10046 trace name context forever, level 12".
For a specific session (not your current session) , after identifying the sid and serial# from v$session
exec sys.dbms_system.set_ev(75,5029,10046,4,NULL);
If you cannot alter the source code or otherwise enable SQL trace from within the session, you can do it from a SYSDBA session:
SQL*Plus ORADEBUG
SQL> oradebug setospid <OS-pid-of-server-process>
SQL> oradebug event 10046 trace name context forever, level 4
Using the DBMS_SYSTEM package
exec dbms_system.set_ev(<sid>,<serial#>, 10046,4,'');
Tim Gorman has a PL/SQL procedure named TRCLVL12 posted on his website www.EvDBT.com/tools.htm which demonstrates the use of the SET_EV procedure. He also have UNIX korn-shell scripts named "traceon.sh" and "traceoff.sh" at the same website which demonstrate the use of ORADEBUG. However, be aware that both utilities are setting SQL trace level 12, which is a combination of level 4 (bind variables) and level 8 (display wait event info) and produces huge trace files.
I picked up the following suggestion from Steve Orr which is worthy of investigation.
CREATE OR REPLACE TRIGGER dbmon_logon
AFTER LOGON ON DBMON.SCHEMA
BEGIN
DBMS_SESSION.SET_SQL_TRACE (TRUE) ;
END ;
/
To toggle everything on & off
alter trigger dbmon_logon enable/disable ;