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;