Oracle DBMS_SQLQ_INTERNAL
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 SQL Quarantine Internal Support Utilities
AUTHID DEFINER
Dependencies
DBMS_ASSERT DBMS_SQLTUNE_UTIL0 SQLOBJ$
DBMS_SMB_INTERNAL DBMS_STANDARD SQLOBJ$AUXDATA
DBMS_SQLCONTROL_INTERNAL DBMS_SYS_ERROR SQLOBJ$DATA
DBMS_SQLCONTROL_LIB DUAL V_$SQL
DBMS_SQLQ PLITBLM XMLTYPE
DBMS_SQLTUNE_INTERNAL SQL$TEXT  
Documented No
Exceptions
Error Code Reason
ORA-38133 Invalid parameter name <string> specified
ORA-38134 Invalid parameter value <string> specified
First Available 19.1
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/prvsssqlqi.plb
Subprograms
 
ALTER_QUARANTINE
Change the value of a quarantine parameter dbms_sqlq_internal.alter_quarantine(
quarantine_name IN VARCHAR2,
parameter_name  IN VARCHAR2,
parameter_value IN VARCHAR2);
TBD
 
AUTO_CREATE_QUARANTINE
Create a quarantine specification dbms_sqlq_internal.auto_create_quarantine(
signature       IN NUMBER,
plan_hash_value IN NUMBER,
parameter_name  IN VARCHAR2,
parameter_value IN VARCHAR2);
TBD
 
AUTO_PURGE_QUARANTINE
Returns 1 if quarantined SQL was purged, else 0 dbms_sqlq_internal.auto_purge_quarantine RETURN NUMBER;
SELECT dbms_sqlq_internal.auto_purge_quarantine
FROM dual;

AUTO_PURGE_QUARANTINE
---------------------
                    0
 
CREATE_QUARANTINE_BY_SQL_ID
Quarantine a SQL statement and return the quarantine name dbms_sqlq_internal.create_quarantine_by_sql_id(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER)
RETURN VARCHAR2;
SELECT sql_id, plan_hash_value
FROM v$sqlarea
WHERE rownum < 6;

SQL_ID PLAN_   HASH_VALUE
------------- -----------
94qn6y14kw01g  1388734953
1uw84jcq6802a   591542025
2z0udr4rc402m  2709293936
2z0udr4rc402m  2709293936
gngtvs38t0060  3679945446


SELECT dbms_sqlq_internal.create_quarantine_by_sql_id('94qn6y14kw01g',1388734953)
FROM dual;

DBMS_SQLQ_INTERNAL.CREATE_QUARANTINE_BY_SQL_ID('94QN6Y14KW01G',1388734953)
----------------------------------------------------------------------------
SQL_QUARANTINE_8httnzbq07zzk52c669e9
 
CREATE_QUARANTINE_BY_SQL_TEXT
Quarantine a SQL statement and return the quarantine name dbms_sqlq_internal.create_quarantine_by_sql_text(
sql_text        IN CLOB,
plan_hash_value IN NUMBER)
RETURN VARCHAR2;
SELECT sql_text, plan_hash_value
FROM v$sqlarea
WHERE rownum < 6;

SQL_TEXT
----------------------------------------------------------------------------
PLAN_HASH_VALUE
---------------
SELECT NVL(TO_NUMBER(EXTRACT(XMLTYPE(:B2 ), :B1 )), 0) FROM DUAL
1388734953

select inst_id,hxfil, decode(hxerr, 0,decode(bitand(fhsta, 1), 0,'NOT ACTIVE','ACTIVE'), 1,'FILE MISSING', 2,'OFFLINE NORMAL', 3,'NOT VERIFIED', 4,'FILE NOT FOUND', 5,'CANNOT OPEN F
ILE', 6,'CANNOT READ HEADER', 7,'CORRUPT HEADER', 8,'WRONG FILE TYPE', 9,'WRONG DATABASE', 10,'WRONG FILE NUMBER', 11,'WRONG FILE CREATE', 12,'WRONG FILE CREATE', 16,'DELAYED OPEN',
'UNKNOWN ERROR'), to_number(fhbsc), to_date(fhbti,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), con_id from x$kcvfhonl
591542025

select exptime, ltime, astatus, lcount from user$ where user#=:1
2709293936

select exptime, ltime, astatus, lcount from user$ where user#=:1
2709293936

SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FROM sys.sysauth$ s CONNECT BY s.grantee# = PRIOR s.privilege# AND (s.privilege# > 0 OR s.priv
ilege# = -352) START WITH (s.privilege# > 0 OR s.privilege# = -352) AND s.grantee# IN (SELECT c1.privilege# FROM sys.codeauth$ c1 WHERE c1.obj# = :1
) UNION SELECT c2.privilege# FROM sys.codeauth$ c2 WHERE c2.obj# = :2 ORDER BY 1 ASC
3679945446


SELECT dbms_sqlq_internal.create_quarantine_by_sql_text('select exptime, ltime, astatus, lcount from user$ where user#=:1', 2709293936)
FROM dual;

DBMS_SQLQ_INTERNAL.CREATE_QUARANTINE_BY_TEXT('94QN6Y14KW01G',1388734953)
----------------------------------------------------------------------------
SQL_QUARANTINE_b1kr2xv0pg6uga17c8b70
 
DROP_QUARANTINE
Drops the identified quarantine object dbms_sqlq_internal.drop_quarantine(quarantine_name IN VARCHAR2);
exec dbms_sqlq_internal.drop_quarantine('SQL_QUARANTINE_8httnzbq07zzk52c669e9');
 
GET_PARAM_VALUE_QUARANTINE
Returns the value of a SQL quarantine parameter. dbms_sqlq_internal.get_param_value_quarantine(
quarantine_name IN VARCHAR2,
parameter_name  IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_sqlq_internal.get_param_value_quarantine('SQL_QUARANTINE_b1kr2xv0pg6uga17c8b70', 'CPU_TIME') AS RetVal
FROM dual;

RETVAL
-------
ALWAYS

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_SQLCONTROL_INTERNAL
DBMS_SQLQ
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