Use dbms_monitor.session_trace_enable to track a session

I. four levels of the 10046 event

10046 events can be divided into four levels according to the content of the information collected:

Level 1: equivalent to SQL_TRACE

Level 4: add information to collect binding variables on the basis of Level 1

Level 8: add wait event information to Level 1

Level 12: equivalent to Level 4+Level 8, which collects both binding variable information and wait event information.

two。

The following PL/SQL is ID. The session with serial number 6734 starts the SQL trace at level 8:

Begin

Dbms_monitor.session_trace_enable (session_id = > 122)

Serial_num = > 6734

Waits = > true

Binds = > false)

End

Session_id

Session logo. The SID column in the corresponding v$session view. Here is how to get the current session id:

S elect sid from v$version

Serial_num

The SERIAL# column in the corresponding v$session view, because the SID is reused. When SID is reused, SERIAL# is added. The acquisition method is as follows: select serial# from v$session

Waits

The SQL_TRACE_WAITS in the corresponding v$session view indicates whether waiting for event tracking is activated or not. It defaults to true.

Binds

The SQL_TRACE_BINDS in the corresponding v$session view, indicating whether the binding trace is activated. Default false.

When the operation is successful. The SQL_TRACE in the v$session view is changed to the corresponding values set for you by ENABLED,SQL_TRACE_WAITS and SQL_TRACE_BINDS.

The following PL/SQL is used to turn off SQL tracing:

1.begin

Dbms_monitor.session_trace_disable (session_id = > 122, serial_num = > 6734)

End

2 modify the file name of the trace file:

Alter session set tracefile_identifier='10046'

3. Use

Select tracefile from v$process where addr= (select paddr from v$session where sid= (select distinct sid from v$mystat))

Determine the trace file.

4. Use tools to convert to txt format

Execute in the operating system: name of the tkprof trace file (for example: RBKSAFARI_ora_30598.trc) new.txt

One thing to note when using dbms_monitor.session_trace_enable is that the traced SQL statement must be the current instance and the session cannot be traced across instances.