Oracle Database - Un-necessary Tracing Turned on Database / What events are currently traced

In this blog - I am going to write about how to know what all tracing is turned on, on your Database Server. 


Let us say you come in and see quite a lot of traces been generated in your background Dump Destination.
You want to know what kind of events have been enabled for tracing on the database server. 

 -- set tracefile name to make the file easier to locate:

SQL> alter session set tracefile_identifier='MY_ACTIVE_SYSTEM_EVENTS';


-- Connect oradebug to the current database session
SQL> oradebug setmypid
Statement processed.


-- Ask oradebug to identify the current trace file
SQL> oradebug tracefile_name
/app/oracle/diag/rdbms/MYDB/MYSID/trace/MYSID_ora_11683_MYACTIVE_SYSTEM_EVENTS.trc

 -- Dump events defined at the SYSTEM level to the active trace file:

SQL> alter session set events 'immediate eventdump(system)';
Session altered.

Example of Trace File -
Dumping Event (group=SYSTEM)
sql_trace level=1
12099 trace name context forever, level 1

if you enable SQL Trace at Session level and dump Events Traces at Session Level 


SQL> alter session set events 'immediate eventdump(session)';
Session altered.

you get something like this below in the trace

Dumping Event (group=SESSION)
trace [RDBMS.SQL_OPTIMIZER]
trace [RDBMS.SQL_Transform]
trace [RDBMS.SQL_MVRW]
trace [RDBMS.SQL_VMerge]
trace [RDBMS.SQL_Virtual]
trace [RDBMS.SQL_APA]
trace [RDBMS.SQL_Costing] 
trace [RDBMS.SQL_Parallel_Optimization]
trace [RDBMS.SQL_Plan_Management] 
trace [RDBMS.SQL_Plan_Directive] 


More details are available using oradebug and other available arguements at
 - How To List All The Named Events Set For A Database (Doc ID 436036.1)