Oracle DBMS_MONITOR
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located in North America that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
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);

Related Topics
Built-in Functions
Built-in Packages
DBMS_SERVICE
DBMS_SESSION
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
TKPROF & TRACE
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2022 Daniel A. Morgan All Rights Reserved
  DBSecWorx