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
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);