Oracle | DBMS_SQLQ

Oracle DBMS_SQLQ_INTERNAL 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 anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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	CREATE_QUARANTINE_BY_SQL_ID	DROP_QUARANTINE
AUTO_CREATE_QUARANTINE	CREATE_QUARANTINE_BY_SQL_TEXT	GET_PARAM_VALUE_QUARANTINE
AUTO_PURGE_QUARANTINE

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