Oracle DBMS_SQLQ_INTERNAL
Version 19.3

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_STANDARD SQLOBJ$
DBMS_SMB_INTERNAL DBMS_SYS_ERROR SQLOBJ$AUXDATA
DBMS_SQLCONTROL_INTERNAL DUAL SQLOBJ$DATA
DBMS_SQLCONTROL_LIB PLITBLM V_$SQL
DBMS_SQLQ SQL$TEXT XMLTYPE
DBMS_SQLTUNE_INTERNAL    
Documented No
Exceptions
Error Code Reason
ORA-38133 Invalid parameter name <string> specified
ORA-38134 Invalid parameter value <string> specified
First Available 19c
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/prvsssqlqi.plb
Subprograms
 
I_ALTER_QUARANTINE (new 19c)
Change the value of a quarantine parameter dbms_sqlq_internal.i_alter_quarantine(
QUARANTINE_NAME IN VARCHAR2,
PARAMETER_NAME  IN VARCHAR2,
PARAMETER_VALUE IN VARCHAR2);
TBD
 
I_AUTO_CREATE_QUARANTINE (new 19c)
Create a quarantine specification dbms_sqlq_internal.i_auto_create_quarantine(
SIGNATURE       IN NUMBER,
PLAN_HASH_VALUE IN NUMBER,
PARAMETER_NAME  IN VARCHAR2,
PARAMETER_VALUE IN VARCHAR2);
TBD
 
I_AUTO_PURGE_QUARANTINE (new 19c)
Returns 1 if quarantined SQL was purged, else 0 dbms_sqlq_internal.i_auto_purge_quarantine RETURN NUMBER;
SELECT dbms_sqlq_internal.i_auto_purge_quarantine
FROM dual;

I_AUTO_PURGE_QUARANTINE
-----------------------
                      0
 
I_BUILD_NEW_HINTS_XML (new 19c)
Undocumented dbms_sqlq_internal.i_build_new_hints_xml(
SIGNATURE       IN     NUMBER,
PLAN_HASH_VALUE IN     NUMBER,
CATEGORY        IN     VARCHAR2,
PARAM_NAME      IN     VARCHAR2,
PARAM_VALUE     IN     VARCHAR2,
NEW_HINTS       IN OUT CLOB);
TBD
 
I_CREATE_QUARANTINE_BY_SQL_ID (new 19c)
Quarantine a SQL statement and return the quarantine name dbms_sqlq_internal.i_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.i_create_quarantine_by_sql_id('94qn6y14kw01g',1388734953)
FROM dual;

DBMS_SQLQ_INTERNAL.I_CREATE_QUARANTINE_BY_SQL_ID('94QN6Y14KW01G',1388734953)
----------------------------------------------------------------------------
SQL_QUARANTINE_8httnzbq07zzk52c669e9
 
I_CREATE_QUARANTINE_BY_TEXT (new 19c)
Quarantine a SQL statement and return the quarantine name dbms_sqlq_internal.i_create_quarantine_by_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.i_create_quarantine_by_text('select exptime, ltime, astatus, lcount from user$ where user#=:1', 2709293936)
FROM dual;

DBMS_SQLQ_INTERNAL.I_CREATE_QUARANTINE_BY_TEXT('94QN6Y14KW01G',1388734953)
----------------------------------------------------------------------------
SQL_QUARANTINE_b1kr2xv0pg6uga17c8b70
 
I_DROP_QUARANTINE (new 19c)
Drops the identified quarantine object dbms_sqlq_internal.i_drop_quarantine(quarantine_name IN VARCHAR2);
exec dbms_sqlq_internal.i_drop_quarantine('SQL_QUARANTINE_8httnzbq07zzk52c669e9');
 
I_FLUSH_QUARANTINE (new 19c)
Flushes SQL Quarantine dbms_sqlq_internal.i_flush_quarantine;
exec dbms_sqlq_internal.i_flush_quarantine;
 
I_GET_PARAM_VALUE_QUARANTINE (new 19c)
Returns the value of a SQL quarantine parameter. dbms_sqlq_internal.i_get_param_value_quarantine(
QUARANTINE_NAME IN VARCHAR2,
PARAMETER_NAME  IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_sqlq_internal.i_get_param_value_quarantine('SQL_QUARANTINE_b1kr2xv0pg6uga17c8b70', 'CPU_TIME') AS RetVal
FROM dual;

RETVAL
---------
ALWAYS
 
I_VALIDATE_PARAMETER (new 19c)
Returns the value if valid, otherwise raises an exception dbms_sqlq_internal.i_validate_parameter(
PARAMETER_NAME   IN VARCHAR2,
PARAMETER_VALUE  IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_sqlq_internal.i_validate_parameter('IO_MEGABYTES', '10')
FROM dual;

DBMS_SQLQ_INTERNAL.I_VALIDATE_PARAMETER('IO_MEGABYTES','10')
------------------------------------------------------------
10

SELECT dbms_sqlq_internal.i_validate_parameter('IO_MEGABYTES', 'ZZYZX')
FROM dual;
SELECT dbms_sqlq_internal.i_validate_parameter('IO_MEGABYTES', 'ZZYZX')
*
ERROR at line 1:
ORA-38134: invalid parameter value ZZYZX specified
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SQLQ_INTERNAL", line 603
 
I_VALIDATE_PARAMETER_NAME (new 19c)
Returns TRUE if a parameter is valid, otherwise raises an exception ... does not appear to return FALSE. dbms_sqlq_internal.i_validate_parameter_name(parameter_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_sqlq_internal.i_validate_parameter_name('IO_MEGABYTES') THEN
    dbms_output.put_line('I am valid a parameter');
  ELSE
    dbms_output.put_line('I am am invalid parameter');
  END IF;
END;
/
SQL> BEGIN
2 IF dbms_sqlq_internal.i_validate_parameter_name('IO_MEGABYTES') THEN
3 dbms_output.put_line('I am valid a parameter');
4 ELSE
5 dbms_output.put_line('I am am invalid parameter');
6 END IF;
7 END;
8 /
I am valid a parameter

PL/SQL procedure successfully completed.

When it does not have a vlaid parameter it raises ORA-38133

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_SQLCONTROL_INTERNAL
DBMS_SQLQ
What's New In 18c
What's New In 19c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx