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
Provides information about Real-time SQL and Database Operation Monitoring.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
Date Format
DATE_FMT
VARCHAR2(21)
'mm/dd/yyyy hh24:mi:ss'
Forced Tracking
FORCE_TRACKING
VARCHAR2(30)
'Y'
NO_FORCE_TRACKING
VARCHAR2(30)
'N'
Monitor Types
MONITOR_TYPE_SQL
NUMBER
1
MONITOR_TYPE_DBOP
NUMBER
2
MONITOR_TYPE_ALL
NUMBER
3
Report Levels
LEVEL_ALL
VARCHAR2(3)
'ALL'
LEVEL_BASIC
VARCHAR2(5)
'BASIC'
LEVEL_TYPICAL
VARCHAR2(7)
'TYPICAL'
Report Sections
SECTION_ALL
VARCHAR2(3)
'ALL'
SECTION_ERRORS
VARCHAR2(6)
'ERRORS'
SECTION_FINDINGS
VARCHAR2(8)
'FINDINGS'
SECTION_INFORMATION
VARCHAR2(11)
'INFORMATION'
SECTION_PLANS
VARCHAR2(5)
'PLANS'
SECTION_SUMMARY
VARCHAR2(7)
'SUMMARY'
Report Types
TYPE_HTML
VARCHAR2(4)
'HTML'
TYPE_TEXT
VARCHAR2(4)
'TEXT'
TYPE_XML
VARCHAR2(3)
'xML'
Dependencies
CDB_SQL_MONITOR_USAGE
DBMS_SQLMON_LIB
GV$SQL_MONITOR
DBA_SQL_MONITOR_USAGE
DBMS_SQLTUNE
GV$SQL_MONITOR_STATNAME
DBMS_DEBUG_JDWP
DBMS_SQLTUNE_UTIL1
KUPM$MCP
DBMS_HPROF
DBMS_SYS_ERROR
XMLTYPE
DBMS_REPORT
DUAL
Documented
Yes
Exceptions
Error Code
Reason
ORA-13812
Current session does not have database operation monitored
First Available
12.1
Security Model
Owned by SYS with EXECUTE granted to PUBLIC. The reporting functionality requires the SELECT_CATALOG_ROLE role.
If anyone can think of a reason why EXECUTE should be granted to PUBLIC rather than a privileged account I would appreciate an email with that explanation.
Starts a composite database operation in the current session
dbms_sql_monitor.begin_operation(
dbop_name IN VARCHAR2,
dbop_eid IN NUMBER := NULL,
forced_tracking IN VARCHAR2 := NO_FORCE_TRACKING,
attribute_list IN VARCHAR2 := NULL,
session_id IN NUMBER := NULL,
session_serial IN NUMBER := NULL)
RETURN NUMBER;
DECLARE
n NUMBER;
BEGIN
n := dbms_sql_monitor.begin_operation('UWMonOps', attribute_list => 'table_name = sys.zzyzx, operation=load');
dbms_output.put_line(TO_CHAR(n));
END;
/ 1
PL/SQL procedure successfully completed.
SELECT UNIQUE dbop_name
FROM gv$sql_monitor;
DBOP_NAME
------------------------------
UWMonOps
CREATE TABLE zzyzx AS
SELECT * FROM dba_objects
WHERE 1=2;
Table created.
INSERT INTO sys.zzyzx SELECT * FROM dba_objects;
78005 rows created.
COMMIT;
Commit complete.
INSERT /*+APPEND*/ INTO sys.zzyzx SELECT * FROM dba_objects;
78005 rows created.
COMMIT;
Commit complete.
DECLARE
x XMLTYPE;
BEGIN
x := dbms_sql_monitor.report_sql_monitor_xml(dbop_name=>'UWMonOps');
dbms_output.put_line(x.getCLOBVal());
END;
/
Builds a detailed report for a specific database operation that has been monitored by Oracle
dbms_sql_monitor.report_sql_monitor(
sql_id IN VARCHAR2 DEFAULT NULL,
dbop_name IN VARCHAR2 DEFAULT NULL,
dbop_exec_id IN NUMBER DEFAULT NULL,
session_id IN NUMBER DEFAULT NULL,
session_serial IN NUMBER DEFAULT NULL,
sql_exec_start IN DATE DEFAULT NULL,
sql_exec_id IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
start_time_filter IN DATE DEFAULT NULL,
end_time_filter IN DATE DEFAULT NULL,
instance_id_filter IN NUMBER DEFAULT NULL,
parallel_filter IN VARCHAR2 DEFAULT NULL,
plan_line_filter IN NUMBER DEFAULT NULL,
event_detail IN VARCHAR2 DEFAULT 'YES',
bucket_max_count IN NUMBER DEFAULT 128,
bucket_interval IN NUMBER DEFAULT NULL,
base_path IN VARCHAR2 DEFAULT NULL,
last_refresh_time IN DATE DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
type IN VARCHAR2 DEFAULT 'TEXT',
sql_plan_hash_value IN NUMBER DEFAULT NULL,
con_name IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
DECLARE
c CLOB;
BEGIN
c := dbms_sql_monitor.report_sql_monitor(dbop_name=>'UWMonOps');
dbms_output.put_line(c);
END;
/
SQL Monitoring Report
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SYS (13:117)
DBOP Name : UWMonOps
DBOP Execution ID : 1
First Refresh Time : 04/10/2021 21:47:33
Last Refresh Time : 04/10/2021 21:48:20
Duration : 47s
Module/Action : sqlplus.exe/-
Service : SYS$USERS
Program : sqlplus.exe
PLSQL Entry Ids (Object/Subprogram) : 10702,3
PLSQL Current Ids (Object/Subprogram) : 10544,61
Builds a report for all or a subset of database operations that have been monitored by Oracle
dbms_sql_monitor.report_sql_monitor_list(
sql_id IN VARCHAR2 DEFAULT NULL,
dbop_name IN VARCHAR2 DEFAULT NULL,
monitor_type IN NUMBER DEFAULT MONITOR_TYPE_ALL,
session_id IN NUMBER DEFAULT NULL,
session_serial IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
active_since_date IN DATE DEFAULT NULL,
active_since_sec IN NUMBER DEFAULT NULL,
last_refresh_time IN DATE DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
auto_refresh IN NUMBER DEFAULT NULL,
base_path IN VARCHAR2 DEFAULT NULL,
type IN VARCHAR2 DEFAULT 'TEXT',
con_name IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
DECLARE
c CLOB;
BEGIN
c := dbms_sql_monitor.report_sql_monitor_list(dbop_name => 'UWMonOps');
dbms_output.put_line(c);
END;
/
Builds a report for all or a subset of database operations that have been monitored by Oracle in XML
dbms_sql_monitor.report_sql_monitor_list_xml(
sql_id IN VARCHAR2 DEFAULT NULL,
dbop_name IN VARCHAR2 DEFAULT NULL,
monitor_type IN NUMBER DEFAULT MONITOR_TYPE_ALL,
session_id IN NUMBER DEFAULT NULL,
session_serial IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
active_since_date IN DATE DEFAULT NULL,
active_since_sec IN NUMBER DEFAULT NULL,
last_refresh_time IN DATE DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
auto_refresh IN NUMBER DEFAULT NULL,
base_path IN VARCHAR2 DEFAULT NULL,
con_name IN VARCHAR2 DEFAULT NULL)
RETURN XMLTYPE;
DECLARE
x XMLTYPE;
BEGIN
x := dbms_sql_monitor.report_sql_monitor_list_xml('UWMonOps');
dbms_output.put_line(x.getCLOBVal());
END;
/
Builds a detailed report for a specific database operation that has been monitored by Oracle in XML
dbms_sql_monitor.report_sql_monitor_xml(
sql_id IN VARCHAR2 DEFAULT NULL,
dbop_name IN VARCHAR2 DEFAULT NULL,
dbop_exec_id IN NUMBER DEFAULT NULL,
session_id IN NUMBER DEFAULT NULL,
session_serial IN NUMBER DEFAULT NULL,
sql_exec_start IN DATE DEFAULT NULL,
sql_exec_id IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
start_time_filter IN DATE DEFAULT NULL,
end_time_filter IN DATE DEFAULT NULL,
instance_id_filter IN NUMBER DEFAULT NULL,
parallel_filter IN VARCHAR2 DEFAULT NULL,
plan_line_filter IN NUMBER DEFAULT NULL,
event_detail IN VARCHAR2 DEFAULT 'YES',
bucket_max_count IN NUMBER DEFAULT 128,
bucket_interval IN NUMBER DEFAULT NULL,
base_path IN VARCHAR2 DEFAULT NULL,
last_refresh_time IN DATE DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
auto_refresh IN NUMBER DEFAULT NULL,
sql_plan_hash_value IN NUMBER DEFAULT NULL,
con_name IN VARCHAR2 DEFAULT NULL)
RETURN XMLTYPE;