Thursday, July 30, 2009

How to: enable oracle sql trace part 1 - current session

The levels available in Oracle through some of the interfaces used to set trace are:

  • Level 0 = No statistics generated
  • Level 1 = standard trace output including parsing, executes and fetches plus more.
  • Level 2 = Same as level 1.
  • Level 4 = Same as level 1 but includes bind information
  • Level 8 = Same as level 1 but includes wait's information
  • Level 12 = Same as level 1 but includes binds and waits


Method 1: using sql_trace


SQL> alter session set sql_trace=true;
Session altered.

SQL> alter session set sql_trace=false;
Session altered.


Method 2: using session level events 10046


SQL> alter session set events '10046 trace name context forever, level 12';
System altered.

SQL> alter session set events '10046 trace name context off';
Session altered.


Method 3: using dbms_session.set_sql_trace


SQL> exec dbms_session.set_sql_trace(true);
PL/SQL procedure successfully completed.

SQL> exec dbms_session.set_sql_trace(false);


Method 4: using dbms_support package


SQL> @?/rdbms/admin/dbmssupp.sql
Package created.
Package body created.

SQL> exec dbms_support.start_trace;
PL/SQL procedure successfully completed.

SQL> exec dbms_support.stop_trace;