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/