Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use dbms_monitor to track multiple sessions in the database, service_name,sid, etc.", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use dbms_monitor to track multiple sessions in the database, service_name,sid, etc." this article.

Overview

In a multi-tier environment with connection pooling or shared servers, a session can span multiple processes or even multiple instances. DBMS_MONITOR is a built-in package introduced in Oracle 10g that allows you to track the session of any user from the client to the middle tier to the back-end database. Because each session produces a separate trace file, it is easier to identify specific users who create a large amount of work. DBMS_MONITOR replaces traditional tracking tools such as DBMS_SUPPORT. You need to have a DBA role to use DBMS_MONITOR.

End-to-end application tracking can be based on the following:

Session (SID): based on session ID (SID) and serial number.

Client identifier (client_identifier): allows tracing to be set across multiple sessions. Specifies the end user based on the login ID. Use the DBMS_SESSION.SET_IDENTIFIER procedure to set this value.

Instance (INST_NAME): specifies the given instance based on the instance name.

Service name (SERVICE_NAME): specifies a set of related applications. Use the DBMS_SERVICE.CREATE_SERVICE procedure to set this value.

Module name (MODULE): developers use the DBMS_APPLICATION_INFO.SET_MODULE procedure to set this value in their application code. Use this name to indicate the module or code being executed.

Operation name (ACTION): developers use the DBMS_APPLICATION_INFO.SET_ACTION procedure to set this value in their application code. Use this name to represent the actions performed by the module.

You can also perform the following sql:

Select sid,serial#,SERVICE_NAME,MODULE,ACTION,client_identifier from v$session

The last three tracking options are hierarchically associated; you cannot specify an operation name without specifying a module name and a service name, but you can specify only the service name, or only the service name and module name.

1. Set tracking based on session ID and serial number (serial# can be added or not)

To set up tracing based on session ID and sequence number, first determine the SID and sequence number of the session that needs to be traced:

SQL > COL PROGRAM FOR A30

SQL > COL EVENT FOR A30

SQL > select sid,serial#,username,program,status,type,event,blocking_session from v$session where type='USER'

SID SERIAL# USERNAME PROGRAM STATUS TYPE EVENT BLOCKING_SESSION

-- --

1 5 SYS sqlplus@wang (TNS V1-V3) ACTIVE USER SQL*Net message to client

37 123 SYS plsqldev.exe INACTIVE USER SQL*Net message from client

38 133 SYS plsqldev.exe INACTIVE USER SQL*Net message from client

39 215 SYS plsqldev.exe INACTIVE USER SQL*Net message from client

41 79 HR sqlplus.exe INACTIVE USER SQL*Net message from client

To enable tracing, execute the following statement:

The third parameter is used to wait (default is TRUE), and the fourth parameter is used to bind variables (default is FALSE).

SQL > exec dbms_monitor.session_trace_enable (1, 5, 4, 4, 5, 5, 4, 5, 4, 5, 4, 5, 4, 4, 5, 4, 5, 4, 4, 4, 5, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,

PL/SQL procedure successfully completed.

SQL > select sid from v$mystat where rownum=1

SID

-

one

SQL > select count (*) from hr.jobs

COUNT (*)

-

nineteen

To turn off tracing, execute the following statement:

SQL > exec dbms_monitor.session_trace_disable (1BI 5)

PL/SQL procedure successfully completed.

-- query the location of trace files:

Select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace

FROM v$process a, v$session b, v$parameter c, v$instance d

WHERE a.addr = b.paddr

AND b.audsid = userenv ('sessionid')

AND c.name = 'user_dump_dest'

Perform as follows:

SQL > select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace

2 FROM v$process a, v$session b, v$parameter c, v$instance d

3 WHERE a.addr = b.paddr

4 AND b.audsid = userenv ('sessionid')

5 AND c.name = 'user_dump_dest'

TRACE

-

/ u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_5994.trc

Use the vi tool to view, / dbms_monitor.session_trace button nhand N

To track the current session, set SID and SERIAL# to empty:

SQL > exec dbms_monitor.session_trace_enable (null,null)

two。 Set up tracking based on client identifier

To set up tracing based on the client identifier that represents the user, run the following statement:

SQL > exec dbms_session.set_identifier ('bryan id')

Or execute the following sql:

Select sid,serial#,username,client_identifier from v$session

To verify the client identifier, execute the following statement:

Select sid,serial#,username,client_identifier from v$session

SID SERIAL# USERNAME CLIENT_IDENTIFIER

1 5 SYS bryan id

You can now set the trace for this client identifier: the second parameter is for waiting (default is TRUE) and the third parameter is for binding variables (default is FALSE).

SQL > exec dbms_monitor.client_id_trace_enable ('bryan id',true,true)

PL/SQL procedure successfully completed.

-- perform some actions:

SQL > create table an as select * from dba_objects where rownum insert into a select * from a

9 rows created.

SQL > commit

Commit complete.

SQL > select count (*) from a

COUNT (*)

-

eighteen

To disable this client identifier tracking, execute the following statement:

SQL > exec dbms_monitor.client_id_trace_disable ('bryan id')

PL/SQL procedure successfully completed.

-- query the location of trace files:

Select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace

FROM v$process a, v$session b, v$parameter c, v$instance d

WHERE a.addr = b.paddr

AND b.audsid = userenv ('sessionid')

AND c.name = 'user_dump_dest'

SQL > select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace

2 FROM v$process a, v$session b, v$parameter c, v$instance d

3 WHERE a.addr = b.paddr

4 AND b.audsid = userenv ('sessionid')

5 AND c.name = 'user_dump_dest'

TRACE

/ u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_5994.trc

3. Set the tracking of service name / module name / operation name

In order to use the operation name, there must be a corresponding module name and service name. In order to use the module name, you must have a service name. Enables tracing for a given combination of service name, module name, and operation name for a database globally, unless an instance name is specified for the procedure. The service name is determined by the connection string used to connect to the service.

The Oracle database is represented as a client as a service; that is, the database performs the appropriate operations on behalf of the client. A database can have one or more services associated with it. For example, you can have a database with two different services for Web clients: book.us.acme.com for the client that buys books, and soft.us.acme.com for the client that buys software. In this example, the database name is sales.acme.com, so the service name is not based on the database name. The service name is specified by the SERVICE_NAMES parameter in the initial parameter file. The service name defaults to the global database name that consists of the database name (DB_NAME parameter) and the domain name (DB_DOMAIN parameter).

-- the query service name / module name / operation name is lost using the following:

Select sid,serial#,SERVICE_NAME,MODULE,ACTION,client_identifier from v$session

To enable tracking of service names, execute the following statement:

SQL > exec dbms_monitor.serv_mod_act_trace_enable (service_name= > 'DBdb')

This will track all sessions with the service name DBdb.

To enable tracking of the combination of service name, module name, and operation name, execute the following statement:

SQL > exec dbms_monitor.serv_mod_act_trace_enable (service_name= > 'ebk2', module_name= >' salary_update', action_name= > 'insert_item')

To disable tracing in the previous code, use the procedure SERV_MOD_ACT_TRACE_DISABLE, which is as follows:

SQL > exec dbms_monitor.serv_mod_act_trace_disable (service_name= > 'ebk2', module_name= >' salary_update', action_name= > 'insert_item')

To track the entire database or instance, execute the following statement (not recommended):

Execute DBMS_MONITOR.DATABASE_TRACE_ENABLE (waits = > TRUE, binds = > FALSE, instance_name = > 'ebk1')

Execute DBMS_MONITOR.DATABASE_TRACE_DISABLE (instance_name = > 'ebk1')

Note: when using DBMS_MONITOR, be sure to disable tracing when you complete the operation; otherwise, each session that meets the specified criteria will be traced.

The above is all the contents of the article "how to use dbms_monitor to track multiple sessions in the database, service_name,sid, etc.". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.