Oracle 10g New Dbms_monitor Package (i) Trace-related procedures

In 10g, Oracle has added dbms_monitor packages, making it easier for users to set up trace and statistical information.

This article describes the trace-related process.

The Dbms_monitor package provides several ways to open, close a session, or trace for an entire instance.

In addition to the most commonly used session_trace_enable/session_trace_disable that are set by the SID and Serial# of the session, the database_trace_enable/that sets all sessions on the database instance is also included The database_trace_disable process, which indicates the setting of Session TRACE client_id_trace_enable/client_id_trace_disable through service_name through the client ID, Module_name and Action_name to set the serv_mod_act_trace_enable/serv_mod_act_trace_disable.

Look at a simple example:

Sql> SELECT SID, serial#, Sql_trace, Sql_trace_waits, Sql_trace_binds

2 from V$session

3 WHERE SID in

4 (SELECT SID

5 from V$mystat

6 WHERE rownum = 1);

SID serial# Sql_trac sql_t sql_t

---------- ---------- -------- ----- -----

294 45413 DISABLED False

Set trace information for the current session in a different session:

sql> SET sqlp ' sql2> '

Sql2> Conn/as SYSDBA

is connected.

Sql2> EXEC Dbms_monitor. Session_trace_enable (294, 45413)

The PL/SQL process has completed successfully.

Return to just the session check trace status:

Sql> SELECT SID, serial#, Sql_trace, Sql_trace_waits, Sql_trace_binds

2 from V$session

3 WHERE SID = 294;

SID serial# Sql_trac sql_t sql_t

---------- ---------- -------- ----- -----

294 45413 ENABLED TRUE FALSE

More Wonderful content: http://www.bianceng.cn/database/Oracle/

You can see that by default, the wait event is set without setting the binding variable.

Sql2> EXEC Dbms_monitor. Session_trace_disable (294, 45413)

The PL/SQL process has completed successfully.

After closing, check session state again:

Sql> SELECT SID, Client_identifier, Sql_trace, Sql_trace_waits, Sql_trace_binds

2 from V$session

3 WHERE SID = 294;

SID client_ide Sql_trac sql_t sql_t

---------- ---------- -------- ----- -----

294 DISABLED False

Sql> EXEC dbms_session. Set_identifier (' my_client ')

The PL/SQL process has completed successfully.

Sql> SELECT SID, Client_identifier, Sql_trace, Sql_trace_waits, Sql_trace_binds

2 from V$session

3 WHERE SID = 294;

SID client_ide Sql_trac sql_t sql_t

---------- ---------- -------- ----- -----

294 My_client DISABLED False

The following session is set up in another session where the client is identified as my_client for trace:

Sql2> EXEC Dbms_monitor. Client_id_trace_enable (' My_client ', true, True)

The PL/SQL process has completed successfully.

To check the session trace settings state:

Sql> SELECT SID, Client_identifier, Sql_trace, Sql_trace_waits, Sql_trace_binds

2 from V$session

3 WHERE SID = 294;

SID client_ide Sql_trac sql_t sql_t

---------- ---------- -------- ----- -----

294 My_client DISABLED False

Sql> EXEC dbms_session. Set_identifier (' my_client ')

The PL/SQL process has completed successfully.

Sql> SELECT SID, Client_identifier, Sql_trace, Sql_trace_waits, Sql_trace_binds

2 from V$session

3 WHERE SID = 294;

SID client_ide Sql_trac sql_t sql_t

---------- ---------- -------- ----- -----