Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You may need to enable trace for a session for many reasons. For example, you may need to determine which queries run in a session.
You can enable trace for a session with the DBMS_MONITOR.session_trace_enable procedure. Use of the procedure and the parameters it passes are as follows.
1 2 3 4 5 6 | DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, plan_stat IN VARCHAR2 DEFAULT NULL); |
Example
Enable Trace
The following command can be used to start trace for a session with session id 1153.
1 2 3 | SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1153, waits=>TRUE, binds=>TRUE); PL/SQL procedure successfully completed. |
If “binds => TRUE” is not specified, the variables used during the session will not be written. Therefore, “binds => TRUE” must be specified.
Trace Directory Information
We can find the trace directory information with the following query.
1 2 3 4 5 6 7 8 | SQL> SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = 1153; TRACEFILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/testdb/TESTDB2/trace/TESTDB2_ora_11011484.trc |
Disable Trace
We can use the DBMS_MONITOR.SESSION_TRACE_DISABLE procedure to disable the trace process.
1 2 3 | DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL); |
We can disable the trace we have enabled as follows.
1 2 3 | SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id =>1153); PL/SQL procedure successfully completed. |
Read Trace File
The contents of the trace file will be as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | Trace file /u01/app/oracle/diag/rdbms/testdb/TESTDB2/trace/TESTDB2_ora_11011484.trc Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1 System name: AIX Node name: oradb2 Release: 1 Version: 7 Machine: 00F745F54C00 Instance name: TESTDB2 Redo thread mounted by this instance: 2 Oracle process number: 64 Unix process pid: 11011484, image: oracle@oradb2 *** 2018-11-29 17:23:21.648 *** SESSION ID:(1153.50309) 2018-11-29 17:23:21.648 *** CLIENT ID:() 2018-11-29 17:23:21.648 *** SERVICE NAME:(TESTDB) 2018-11-29 17:23:21.648 *** MODULE NAME:(TOAD 13.0.0.80) 2018-11-29 17:23:21.648 *** ACTION NAME:(59930432,284684816,285133360) 2018-11-29 17:23:21.648 CLOSE #4574988368:c=23,e=39,dep=0,type=1,tim=56146470874665 LOBARRTMPFRE: c=9,e=14,p=0,cr=0,cu=0,tim=56146470889817 ===================== PARSING IN CURSOR #4574986224 len=356 dep=0 uid=150 oct=3 lid=150 tim=56146470890334 hv=2159962230 ad='7000104eb336e90' sqlid='dpt20ua0bwu3q' Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash, decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address , decode(sql_id, null, prev_sql_id, sql_id) sql_id , decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number from v$session where sid = :sid END OF STMT PARSE #4574986224:c=290,e=475,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=56146470890333 ===================== PARSING IN CURSOR #4577893528 len=37 dep=1 uid=0 oct=3 lid=0 tim=56146470891198 hv=1398610540 ad='7000104e38da7b8' sqlid='grwydz59pu6mc' select text from view$ where rowid=:1 END OF STMT PARSE #4577893528:c=315,e=520,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=56146470891198 BINDS #4577893528: Bind#0 oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0 kxsbbbfp=110dd1858 bln=16 avl=16 flg=05 |
