Oracle DBMS_SQLQ
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 This package provides the API for configuring quarantine thresholds for SQL statements and their execution plans. SQL statements that cross the quarantine thresholds are terminated and quarantined. The quarantined SQL statements are not allowed to run again in a database.
AUTHID CURRENT_USER
Constants
Name Data Type Value
General
ALWAYS_QUARANTINE VARCHAR2(30) '4294967295'
DROP_THRESHOLD VARCHAR2(30) '0'
Dependencies
DBMS_SMB DBMS_SQLCONTROL_LIB DBMS_SYS_ERROR
DBMS_SMB_INTERNAL DBMS_SQLQ_INTERNAL  
Documented Yes
Exceptions
Error Code Reason
ORA-06564 Object does not exist
ORA-13825 missing SQL statement text
ORA-31684 Object type SQL Quarantine already exists
ORA-38133 Invalid parameter value specified
ORA-38134 Invalid parameter value specified
First Available 19.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC.

The ADMINISTER SQL MANAGEMENT OBJECT privilege is required.

The grant to PUBLIC is horrifying. Yes the Admin privilege is required but puts this power into the hands of many users that have no business having the ability to perform a DDOS attack.
Source {ORACLE_HOME}/rdbms/admin/dbmssqlq.sql
Subprograms
 
ALTER_QUARANTINE
Alters the quarantine configuration dbms_sqlq.alter_quarantine(
quarantine_name IN VARCHAR2,
parameter_name  IN VARCHAR2,
parameter_value IN VARCHAR2);
Parameter Names
'AUTOPURGE'
'CPU_TIME'
'ELAPSED_TIME'
'ENABLED'
'IO_LOGICAL'
'IO_REQUESTS'
'IO_MEGABYTES'
exec dbms_sqlq.alter_quarantine('TOO_MUCH_DATA', 'IO_MEGABYTES', '1000');
 
CREATE_QUARANTINE_BY_SQL_ID
Creates a SQL quarantine configuration for storing quarantine information using SQL id dbms_sqlq.create_quarantine_by_sql_id(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
exec dbms_sqlq.create_quarantine_by_sql_id('94qn6y14kw01g', NULL);

exec dbms_sqlq.create_quarantine_by_sql_id('d1071nwwx40fg', 970064335)
 
CREATE_QUARANTINE_BY_SQL_TEXT
Creates a SQL quarantine configuration for storing quarantine information using SQL text dbms_sqlq.create_quarantine_by_sql_text(
sql_text        IN CLOB,
plan_hash_value IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
DECLARE
 sqltxt CLOB := 'TRUNCATE TABLE servers';
BEGIN
  retVal := dbms_sqlq.create_quarantine_by_sql_text(sqltxt);
  dbms_output.put_line(retVal);
END;
/
 
CREATE_STGTAB_QUARANTINE
Creates a staging table to pack SQL quarantine configurations dbms_sqlq.create_stgtab_quarantine(
staging_table_name  IN VARCHAR2,
staging_table_owner IN VARCHAR2 DEFAULT NULL,
tablespace_name     IN VARCHAR2 DEFAULT NULL);
exec dbms_sqlq.create_stgtab_quarantine('UWQUARANTINE', 'UWCLASS', 'UWDATA');
 
DROP_QUARANTINE
Drops a quarantine configuration dbms_sqlq.drop_quarantine(quarantine_name IN VARCHAR2);
exec dbms_sqlq.drop_quarantine('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');
 
GET_PARAM_VALUE_QUARANTINE
Returns the value of the quarantine parameter for the given SQL quarantine configuration dbms_sqlq.get_param_value_quarantine(
quarantine_name IN VARCHAR2,
parameter_name  IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_sqlq.get_param_value_quarantine('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', 'IO_MEGABYTES')
FROM dual;
 
PACK_STGTAB_QUARANTINE
Packs SQL quarantine configurations into a staging table dbms_sqlq.pack_stgtab_quarantine(
staging_table_name  IN VARCHAR2,
staging_table_owner IN VARCHAR2 DEFAULT NULL,
name                IN VARCHAR2 DEFAULT '%',
sql_text            IN VARCHAR2 DEFAULT '%',
enabled             IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
SELECT dbms_sqlq.pack_stgtab_quarantine('UW_QSTAGING', 'UWCLASS')
FROM dual;
 
UNPACK_STGTAB_QUARANTINE
Unpacks SQL quarantine configurations from a staging table dbms_sqlq.unpack_stgtab_quarantine(
staging_table_name  IN VARCHAR2,
staging_table_owner IN VARCHAR2 DEFAULT NULL,
name                IN VARCHAR2 DEFAULT '%',
sql_text            IN VARCHAR2 DEFAULT '%',
enabled             IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
SELECT dbms_sqlq.unpack_stgtab_quarantine('UW_QSTAGING', 'UWCLASS')
FROM dual;

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_SQLCONTROL_INTERNAL
DBMS_SQLQ_INTERNAL
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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx