Using dbms_monitor

Using dbms_monitor

 

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

 

       SID    SERIAL# USERNAME

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

       140         57

       145          1

       147          1

       150          1

       153         34 SYS

       155          1

       156          1

       159          5 TEST

       160          1

       161          1

       162          1

 

       SID    SERIAL# USERNAME

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

       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;

 

       SID    SERIAL# USERNAME                       CLIENT_IDENTIFIER

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

       136         37 TEST                           client.liu

       159          5 TEST                           tony.liu

SQL>

 

 

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 : book.us.acme.com for clients making book purchases and soft.us.acme.com for clients making software purchases. In this example ,the database name is sales.acme.com, 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

SERV_MOD_ACT_TRACE_DISABLE, as shown here:

 SQL>exec

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

Dba_enabled_traces

Dba_enabled_aggregations

 

Example:

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

 

PL/SQL procedure successfully completed.

 

SQL> select * from dba_enabled_aggregations;

 

AGGREGATION_TYPE      PRIMARY_ID                                                       QUALIFIER_ID1

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

QUALIFIER_ID2

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

CLIENT_ID             client.liu

 

 

 

Trcsess multiple trace files into one file

 

The oracle 10g allows 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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/104152/viewspace-140037/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/104152/viewspace-140037/