ORADEBUG to Display Trace Name and Location

The Below Example Show you how I did it :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release – Production on Thu Nov 15 17:46:00 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace; –any Statement to Generate Trace File.

Database altered.

SQL> oradebug tracefile_name

Thank you
Osama Mustafa

Some Way To Active Oracle Trace

Reading to Oracle Documentation the trace is facility provides performance information on individual SQL statements. It generates the following statistics for each statement:

  • Parse, execute, and fetch counts
  • CPU and elapsed times
  • Physical reads and logical reads
  • Number of rows processed
  • Misses on the library cache
  • Username under which each parse occurred
  • Each commit and rollback

To enable Oracle Trace you can do the following :

 Option Number One :

alter session set sql_trace = true;

Disable it :

alter session set sql_trace = false;

Option Number two :

dbms_session.set_sql_trace (true);

dbms_session.set_sql_trace (false);

Option Number three:

dbms_support.start_trace (binds=>{true|false}, waits=>{true|false});


Notice that package is not installed by default , Check
EVENT: 10046 “enable SQL statement tracing (including binds/waits)” [ID 21154.1]

There’s The most Common way to Trace your SQL and enable trace , there’s other way you can search on it via Google or Oracle Documentation , all you have to do is trying one of these options .

some Other Useful link :
1-Oracle Documentation 
2-Oracle traces description.
3-Enable Oracle Traces

Thank you

Active Trace Steps

There’s More than One Way to active Oracle Trace , This Topic will talk about how to do this ?

Lets Rock N Roll

1-Alter session set sql _trace Statement 

alter session set sql_trace = true;
Or ( The two Statement is equal )

alter session set events ‘10046 trace name context forever, level 1’;


dbms_session.set_sql_trace (true);

dbms_session.set_sql_trace (false);


dbms_support.start_trace (binds=>{true|false}, waits=>{true|false});

Or to Disable it


4-Alter Session set event
6-Oradebug Command

Ref Link :

Osama Mustafa