Using dbms_monitor

原创 作者:jolly10 时间:2010-12-28 13:49:21 0 删除 编辑
Oracle 10g中提供了一个新的包进行SQL Trace: DBMS_MONITOR。它提供了多种级别的trace[@more@]

SQL> select sid,serial# ,username from v$session;


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

140 57

145 1

147 1

150 1

153 34 SYS

155 1

156 1

159 5 TEST

160 1

161 1

162 1


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

163 1

164 1

165 1

166 1

167 1

168 1

169 1

170 1

19 rows selected.

SQL> exec dbms_monitor.session_trace_enable(159,5,true,false);

The third parameter is for waits( default is TRUE),and the fourth parameter is for bind variables(default is false)

To turn off the trace:

SQL>exec dbms_monitor.session_trace_disable(159,5).

To trace the current session,set the SID the serial# to null;

Exec dbms_monitor.session_trace_enable(null,null);

Setting trace based on client identifier

To set the trace based on client identifier as the user, run the following:

SQL> exec dbms_session.set_identifier('client.liu');

To verify the client identifier,

SQL> select sid,serial#,username,client_identifier from v$session where client_identifier is not null;


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

136 37 TEST client.liu

159 5 TEST tony.liu


Now we can set the trace for this client identifier:

SQL> exec dbms_monitor.client_id_trace_enable('client.liu',true,false);

The second parameter is for waits(default is TRUE), and the third parameter is for bind variables(default is FALSE)

To disable this client identifier trace,

SQL> exec dbms_monitor.client_id_trace_disable('client.liu');

Setting Trace for the Service Name/Module Name/Action Name

In order to use the action name, the module name and the service name must be present, in order to use the module name, the service name must be present. Tracing will be enable for a given combination of service name, module name,and action name globally for a database unless an instance name is specified for a procedure. The service name is is determined by the connect string used to connect to a service.

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. A database can have one or more services associated with it. For example, you could have one database with two different services for web clients : for clients making book purchases and for clients making software purchases. In this example ,the database name is, so the service name isn’t even based on the database name. The service name is specified by the SERVICE_NAMES parameter in the initialization parameter file. The service name defaults to the global database name,a name comprising the database name(DB_NAME parameter) and the domain name(DB_DOMAIN parameter).

To enable tracing for a service name,

SQL>exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’orcl’);

This will trace all sessions with a service name of orcl.

To enable tracing for a combination service,module , and action.

SQL>exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’orcl’,module_name=>’salary_update’,action_name=>’insert_item’);

To Disable tracing in the preceding code, use the procedure



dbms_monitor.serv_mod_act_trce_disable(service_name=>’orcl’, module_name=>’salary_update’,action_name=>’insert_item’);

To trace for entire db or instance(not recommended),

Execute dbms_monitor.database_trace_enable(‘waits=>true,binds=>false,instance_name=>’orcl’);

Enable tracing views




SQL> execute dbms_monitor.client_id_stat_enable('client.liu');

PL/SQL procedure successfully completed.

SQL> select * from dba_enabled_aggregations;


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



CLIENT_ID client.liu

Trcsess multiple trace files into one file

The oracle10gallows trace data to be selectively extracted from multiple trace files and saved into a single trace file based on criteria such as session ID or module name. This command-line utility is especially useful in connection pooling and shared server configurations, where each user request could end up in a separate trace file. TRCSESS lets you obtain consolidated trce information pertaining to a single user session.

Thie consolidated trace file can be created according to several criteria:

Session id

Client id

Service name

Action name

Module name

Example 1

Trcsess utput=trace.trc service=’orcl’

Example 2

Exec dbms_session.set_identifier(‘client’);

Exec dbms_monitor.client_id_stat_enable(‘client’);

Trcsess utput=trace.trc client=client *.trc

请登录后发表评论 登录


  • 博文量
  • 访问量