Oracle | DBMS

Oracle DBMS_MONITOR Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose Replacement for DBMS_SUPPORT this package allows enabling 10046 tracing by session, service, module, and action

AUTHID DEFINER

Constants

Name	Data Type	Value
all_actions	VARCHAR2(14)	'###ALL_ACTIONS'
all_modules	VARCHAR2(14)	'###ALL_MODULES'

Dependencies

DBMS_MONITOR_LIB

Documented Yes: Packages and Types Reference

First Available 10.1

Security Model Owned by SYS with EXECUTE granted to the DBA and OEM_MONITOR roles

Source {ORACLE_HOME}/rdbms/admin/dbmsmntr.sql

Trace File Location Trace files are written to the location ADR Trace directory

SELECT value

      FROM v_$parameter

      WHERE name = 'user_dump_dest';

Subprograms

CLIENT_ID_STAT_DISABLE	DATABASE_TRACE_DISABLE	SERV_MOD_ACT_TRACE_DISABLE
CLIENT_ID_STAT_ENABLE	DATABASE_TRACE_ENABLE	SERV_MOD_ACT_TRACE_ENABLE
CLIENT_ID_TRACE_DISABLE	SERV_MOD_ACT_STAT_DISABLE	SESSION_TRACE_DISABLE
CLIENT_ID_TRACE_ENABLE	SERV_MOD_ACT_STAT_ENABLE	SESSION_TRACE_ENABLE

CLIENT_ID_STAT_DISABLE

Disable previously enabled statistic gathering dbms_monitor.client_id_stat_disable(client_id IN VARCHAR2);

See CLIENT_ID_STAT_ENABLE Demo Below

CLIENT_ID_STAT_ENABLE

Enable statistic gathering for a given Client Identifier dbms_monitor.client_id_stat_enable(client_id IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      col client_identifier format a35

      col service_name format a20

      

      SELECT sid, client_identifier, service_name

      FROM v_$session;

      

      CREATE OR REPLACE TRIGGER logon_trigger

      AFTER LOGON

      ON DATABASE

      DECLARE

       uid VARCHAR2(64);

      BEGIN

        SELECT ora_login_user ||':'|| SYS_CONTEXT('USERENV', 'OS_USER')

        INTO uid

        FROM dual;

      

        dbms_session.set_identifier(uid);

      END logon_trigger;

      /

      

      conn uwclass/uwclass@pdbdev

      

      SELECT sid, client_identifier, service_name

      FROM gv$session;

      

      exec dbms_monitor.client_id_stat_enable('UWCLASS:PERRITO5\Daniel Morgan');

      

      set linesize 161

      col stat_name format a30

      col value format 99999999

      

      SELECT *

      FROM gv$client_stats;

      

      col module format a20

      col action format a20

      

      -- an intentionally bad query

      SELECT COUNT(*)

      FROM all_tables t, all_indexes i

      WHERE t.tablespace_name = i.tablespace_name;

      

      SELECT *

      FROM gv$client_stats;

      

      exec dbms_monitor.client_id_trace_enable('UWCLASS:PERRITO5\Daniel Morgan', TRUE, FALSE);

      

      SELECT COUNT(*)

      FROM all_tables t, all_indexes i

      WHERE t.table_name = i.table_name;

      

      exec dbms_monitor.client_id_trace_disable('UWCLASS:PERRITO5\Daniel Morgan');

      

      -- run TKPROF on trace file

      exec dbms_monitor.client_id_stat_disable('UWCLASS:PERRITO5\Daniel Morgan');

      

      SELECT *

      FROM gv$client_stats;

CLIENT_ID_TRACE_DISABLE

Disables a previously enabled trace dbms_monitor.client_id_trace_disable(client_id IN VARCHAR2);

See CLIENT_ID_STAT_ENABLE Demo Above

CLIENT_ID_TRACE_ENABLE

Enables the trace for a given Client Identifier globally for the database dbms_monitor.client_id_trace_enable( client_id IN VARCHAR2, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, plan_stat IN VARCHAR2 DEFAULT NULL);

See CLIENT_ID_STAT_ENABLE Demo Above

DATABASE_TRACE_DISABLE

Disables SQL trace for the whole database or given instance dbms_monitor.database_trace_disable(instance_name IN VARCHAR2 DEFAULT NULL);

See DATABASE_TRACE_ENABLE Demo Below

DATABASE_TRACE_ENABLE

Enables SQL trace for the whole database or given instance dbms_monitor.database_trace_enable( waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, instance_name IN VARCHAR2 DEFAULT NULL, plan_stat IN VARCHAR2 DEFAULT NULL);

ALTER SESSION SET tracefile_identifier = 'dbms_monitor';

      

      exec dbms_monitor.database_trace_enable(binds=>TRUE);

      

      exec dbms_monitor.database_trace_disable;

SERV_MOD_ACT_STAT_DISABLE

Disables statistic gathering enabled for a given combination of Service Name, MODULE and ACTION dbms_monitor.serv_mod_act_stat_disable( service_name IN VARCHAR2, module_name IN VARCHAR2, action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

See SERV_MOD_ACT_STAT_ENABLEe Demo Below

SERV_MOD_ACT_STAT_ENABLE

Enables statistic gathering for a given combination of Service Name, MODULE and ACTION exec dbms_monitor.serv_mod_act_stat_enable( service_name IN VARCHAR2, module_name IN VARCHAR2, action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

conn uwclass/uwclass@pdbdev

      

      CREATE OR REPLACE PROCEDURE stat_proc IS

       sid   gv$session.sid%TYPE;

       cliid gv$session.client_identifier%TYPE;

       modl  gv$session.module%TYPE;

       act   gv$session.action%TYPE;

      BEGIN

        dbms_session.set_identifier('Morgan:UW');

      

        dbms_application_info.set_module('stat_proc', 'demo');

      

        SELECT sid, client_identifier, module, action

        INTO sid, cliid, modl, act

        FROM gv$session

        WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

      

        dbms_output.put_line('SID: ' || sid);

        dbms_output.put_line('Client Identifier: ' || cliid);

        dbms_output.put_line('Module: ' || modl);

        dbms_output.put_line('Activity: ' || act);

      END stat_proc;

      /

      

      set serveroutput on

      

      exec stat_proc;

      

      conn sys@pdbdev as sysdba

      

      set linesize 141

      col client_identifier format a30

      col service_name format a15

      col module format a15

      col action format a20

      

      SELECT sid, client_identifier, service_name, module, action

      FROM gv$session;

      

      SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name

      FROM gv$serv_mod_act_stats;

      

      exec  dbms_monitor.serv_mod_act_stat_enable('TESTSERV', 'stat_proc', dbms_monitor.ALL_ACTIONS);

      

      col service_name format a20

      col stat_name format a30

      

      SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name

      FROM gv$serv_mod_act_stats;

      

      conn uwclass/uwclass@pdbdev

      

      exec stat_proc;

      

      conn sys@pdbdev as sysdba

      

      SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name

      FROM gv$serv_mod_act_stats;

      

      exec dbms_monitor.serv_mod_act_stat_disable('TESTSERV', 'stat_proc', dbms_monitor.ALL_ACTIONS);

      

      SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name

      FROM gv$serv_mod_act_stats;

SERV_MOD_ACT_TRACE_DISABLE

Globally disables the trace for ALL enabled instances for a given combination of Service Name, MODULE and ACTION name dbms_monitor.serv_mod_act_trace_disable( service_name IN VARCHAR2, module_name IN VARCHAR2 DEFAULT ALL_MODULES, action_name IN VARCHAR2 DEFAULT ALL_ACTIONS, instance_name IN VARCHAR2 DEFAULT NULL);

See SERV_MOD_ACT_TRACE_ENABLE Demo Below

SERV_MOD_ACT_TRACE_ENABLE

Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified dbms_monitor.serv_mod_act_trace_enable( service_name IN VARCHAR2, module_name IN VARCHAR2 DEFAULT ANY_MODULE, action_name IN VARCHAR2 DEFAULT ANY_ACTION, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, instance_name IN VARCHAR2 DEFAULT NULL, plan_stat IN VARCHAR2 DEFAULT NULL);

SELECT instance_name

      FROM gv$instance;

      

      exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase');

      

      exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase');

SESSION_TRACE_DISABLE

Disables the previously enabled trace for a given database session identifier (SID) on the local instance dbms_monitor.SESSION_TRACE_DISABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL);

See SESSION_TRACE_ENABLE Demo Below

SESSION_TRACE_ENABLE

Enables the trace for a given database session identifier (SID) on the local instance DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, plan_stat IN VARCHAR2 DEFAULT NULL);

-- enable tracing for a client with a given client session ID:

SELECT schemaname, sid, serial#

      FROM gv$session;

      

      exec dbms_monitor.session_trace_enable(144, 253, TRUE, FALSE);

      

      -- disable tracing specified in the previous step:

      

      exec dbms_monitor.session_trace_disable(144, 253);

      

      -- either

      

      exec dbms_monitor.session_trace_enable(144);

      

      -- or

      

      exec dbms_monitor.session_trace_enable(144, NULL);

      

      -- traces the session with session ID of 144, while either

      

      exec dbms_monitor.session_trace_enable;

      

      -- or

      

      exec dbms_monitor.session_trace_enable(NULL, NULL);

      

      -- trace the current user session

      

      exec dbms_monitor.session_trace_enable(NULL, NULL, TRUE, TRUE);

      

      -- traces the current user session including waits and binds.

      -- the same can be also expressed using keyword syntax:

      

      exec dbms_monitor.session_trace_enable(binds=>TRUE);

      

      -- enable tracing for a client with a given client session ID:

      

      exec dbms_monitor.session_trace_enable(144, 253, TRUE, FALSE);

      

      -- disable tracing specified in the previous step:

      

      exec dbms_monitor.session_trace_disable(144, 253);