SQL> desc dbms_monitor
Element Type
-------------------------- ---------
ALL_MODULES CONSTANT
ALL_ACTIONS CONSTANT
CLIENT_ID_STAT_ENABLE PROCEDURE
CLIENT_ID_STAT_DISABLE PROCEDURE
SERV_MOD_ACT_STAT_ENABLE PROCEDURE
SERV_MOD_ACT_STAT_DISABLE PROCEDURE
CLIENT_ID_TRACE_ENABLE PROCEDURE
CLIENT_ID_TRACE_DISABLE PROCEDURE
SERV_MOD_ACT_TRACE_ENABLE PROCEDURE
SERV_MOD_ACT_TRACE_DISABLE PROCEDURE
SESSION_TRACE_ENABLE PROCEDURE
SESSION_TRACE_DISABLE PROCEDURE
DATABASE_TRACE_ENABLE PROCEDURE
DATABASE_TRACE_DISABLE PROCEDURE
1. Viewing Enabled Traces
You can query DBA_ENABLED_TRACES to determine what traces are enabled.
Example:
SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds
from DBA_ENABLED_TRACES;
TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 WAITS BINDS
---------------------- --------------- ------------------ -------- -------
SERVICE_MODULE SYS$USERS SQL*Plus TRUE FALSE
CLIENT_ID HUGO TRUE FALSE
SERVICE v101_DGB TRUE FALSE
At this database we have three different trace state.
1.) The first row shows that we trace on the server all SQL statements that are executed in SQL*Plus.
2.) We trace all session that are used in a session with the client identifier 'HUGO'
3.) We trace all program that are connected to the database via the service 'v101_DGB'.
In the next section we explain how we enable and disable tracing.
2. Function session_trace_enable
You can use the SESSION_TRACE_ENABLE procedure to enable SQL tracing for a given database session on the local instance.
Syntax
You enable tracing with
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
and disable tracing with
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);
The default of waits is TRUE and the default of binds is FALSE.
Example
To get the session and the serial numbers you can query V$SESSION.
SQL> select serial#, sid , username from v$session;
SERIAL# SID USERNAME
------- ----- --------------
1 131
18 139
3 140
11 143 SCOTT
Than you can start tracing with the command
SQL> execute dbms_monitor.session_trace_enable(143,11);
This tracing state is not persistent across a database shutdown and you see no entry in DBA_ENABLED_TRACES.
SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds
from DBA_ENABLED_TRACES;
no rows selected
As disconnect from the session or the following command stop tracing
SQL> execute dbms_monitor.session_trace_disable(143,11);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15747463/viewspace-774066/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15747463/viewspace-774066/