Oracle | DBMS_SQLTUNE

Oracle DBMS_SQLTUNE_UTIL0 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 Sqltune internal utility procedures and functions that do not access dictionary objects. Some of these utilities are called as part of upgrade and downgrade scripts.

AUTHID DEFINER

Constants

Name	Data Type	Value
Substitution Patterns
PAT_BEG	VARCHAR2(11)	'$#CDB#$MT1$'
PAT_END	VARCHAR2(11)	$#CDB#$MT2$'

Data Types -- This record represents a remote context with db link to a remote db. -- The remote context is required when executing a remote query. TYPE sqltune_remote_ctx IS RECORD ( db_link_to VARCHAR2(4000) := NULL); -- db link to a remote db -- global variables for remote contexts sqlt_rmt_ctx sqltune_remote_ctx;

Dependencies

DBMS_ASSERT	DBMS_SQLTUNE_UTIL1	PRVT_AWRV_METADATA
DBMS_AUTO_INDEX_INTERNAL	DBMS_STANDARD	PRVT_AWR_VIEWER
DBMS_PERF	DBMS_STATS_INTERNAL	PRVT_REPORT_TAGS
DBMS_SMB	DBMS_SWAT_VER_INTERNAL	PRVT_SQLADV_INFRA
DBMS_SQLDIAG	DBMS_SYS_ERROR	PRVT_WORKLOAD
DBMS_SQLPA	DBMS_XPLAN	SQL_BIND
DBMS_SQLTCB_INTERNAL	PRVTEMX_DBHOME	SQL_BIND_SET
DBMS_SQLTUNE	PRVTEMX_PERF	V$OPTION
DBMS_SQLTUNE_INTERNAL	PRVTEMX_RSRCMGR	WRI$_ADV_SQLTUNE
DBMS_SQLTUNE_LIB	PRVT_ADVISOR

Documented No

Exceptions

Error Code	Reason
ORA-00900	PRAGMA EXCEPTION_INIT(INVALID_SQL, -900);
ORA-65011	Pluggable database CDB$ROOT$ does not exist

First Available 10.1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/dbmssqlu.sql

Subprograms

ADD_SUBST_PATTERN	CDB_IS_ROOT	GET_SUBST_QUERY
CDBCON_DBID_TO_NAME	CHECK_DV_ACCESS	IS_BIND_MASKED
CDBCON_ID_TO_DBID	EXTRACT_BIND	SET_DB_LINK_TO_PRIM
CDBCON_NAME2ID	EXTRACT_BINDS	SQLTEXT_TO_SIGNATURE
CDBCON_NAME2IDS	GET_BINDS_COUNT	SQLTEXT_TO_SQLID
CDB_IS_PDB	GET_DB_LINK_TO_PRIM	VALIDATE_SQLID

ADD_SUBST_PATTERN

Returns pattern added table/view name for remote queries
OR
input table/view name as it is for local queries dbms_sqltune_util0.add_subst_pattern(tbl_name IN VARCHAR2) RETURN VARCHAR2;

conn sys@pdbdev as sysdba

      

      SELECT dbms_sqltune_util0.add_subst_pattern('UWCLASS.SERVERS')

      FROM dual;

CDBCON_DBID_TO_NAME

Returns a container name given a container dbid dbms_sqltune_util0.cdbcon_dbid_to_name(con_dbid IN NUMBER) RETURN VARCHAR2;

SELECT dbid, name

      FROM v$pdbs;

      

      SELECT dbms_sqltune_util0.cdbcon_dbid_to_name(4069235210)

      FROM dual;

CDBCON_ID_TO_DBID

Given a container number

Overload 1 dbms_sqltune_util0.cdbcon_id_to_dbid( con_id IN PLS_INTEGER, con_dbid OUT NUMBER);

show con_id

      

      DECLARE

       outVal NUMBER;

      BEGIN

        dbms_sqltune_util0.cdbcon_id_to_dbid(1, outVal);

        dbms_output.put_line(TO_CHAR(outVal));

      END;

      /

Overload 2 dbms_sqltune_util0.cdbcon_id_to_dbid(con_id IN PLS_INTEGER) RETURN NUMBER;

show con_id

      

      SELECT dbms_sqltune_util0.cdbcon_id_to_dbid(1)

      FROM dual;

CDBCON_NAME2ID (new 21c)

Returns a container id given its name dbms_sqltune_util0.cdbcon_name2id( con_name IN VARCHAR2, con_id OUT PLS_INTEGER);

SELECT name

      FROM v$containers;

      

      NAME

      ----------

      CDB$ROOT

      PDB$SEED

      TEST21P1

      

      DECLARE

        outVal PLS_INTEGER;

      BEGIN

        dbms_sqltune_util0.cdbcon_name2id('PDB$SEED', outVal);

        dbms_output.put_line(outVal);

      END;

      /

      2

      

      PL/SQL procedure successfully completed.

CDBCON_NAME2IDS

Returns a container dbid and container id given a container name

Overload 1 dbms_sqltune_util0.cdbcon_name2ids( con_name IN VARCHAR2, con_id OUT PLS_INTEGER, con_dbid OUT NUMBER);

DECLARE

       outPLS PLS_INTEGER;

       outNUM NUMBER;

      BEGIN

        dbms_sqltune_util0.cdbcon_name2ids('PDBDEV', outPLS, outNUM);

        dbms_output.put_line(TO_CHAR(outPLS));

        dbms_output.put_line(TO_CHAR(outNUM));

      END;

      /

Overload 2 dbms_sqltune_util0.cdbcon_name2ids( con_name IN VARCHAR2, con_id OUT PLS_INTEGER);

DECLARE

       outPLS PLS_INTEGER;

      BEGIN

        dbms_sqltune_util0.cdbcon_name2ids('PDBDEV', outPLS);

        dbms_output.put_line(TO_CHAR(outPLS));

      END;

      /

CDB_IS_PDB

Returns TRUE if this is a PDB in a CDB FALSE is returned for root and for non-CDB dbms_sqltune_util0.cdb_is_pdb( con_name OUT VARCHAR2, con_id OUT NUMBER) RETURN BOOLEAN;

See CDB_IS_ROOT Demo Below

CDB_IS_ROOT

Returns TRUE if this is the root container of a CDB or FALSE is returned for PDBs and for non-CDB dbms_sqltune_util0.cdb_is_root( con_name OUT VARCHAR2, con_id OUT NUMBER) RETURN BOOLEAN;

conn / as sysdba

      

      DECLARE

       cname VARCHAR2(30);

       cid  NUMBER;

      BEGIN

        IF dbms_sqltune_util0.cdb_is_root(cname, cid) THEN

          dbms_output.put_line('ROOT: ' || cname);

          dbms_output.put_line('ROOT: ' || TO_CHAR(cid));

        ELSIF dbms_sqltune_util0.cdb_is_pdb(cname, cid) THEN

          dbms_output.put_line('PDB: ' || cname);

          dbms_output.put_line('PDB: ' || TO_CHAR(cid));

        ELSE

          dbms_output.put_line('No Idea Where I Am');

        END IF;

      END;

      /

      

      conn sys@pdbdev as sysdba

      

      -- run the anonymous block again

CHECK_DV_ACCESS

Checks whether a user has select access on an object when Database Vault is enabled dbms_sqltune_util0.check_dv_access( user_name IN VARCHAR2 := NULL, object_owner IN VARCHAR2, object_name IN VARCHAR2, object_type IN VARCHAR2 := NULL) RETURN IN BINARY_INTEGER ACCESSIBLE BY (PACKAGE SYS.DBMS_SQLTUNE);

Not directly testable due to ACCESSIBLE BY clause

EXTRACT_BIND

Given the value of a bind_data column captured in v$sql and a bind position, this function returns the value of the bind variable at that position in the SQL statement. Bind position start at 1. dbms_sqltune_util0.extract_bind( bind_data IN RAW, bind_pos IN PLS_INTEGER) RETURN SQL_BIND;

desc sys.sql_bind

      

      See GET_BINDS_COUNT Demo Below

EXTRACT_BINDS

Given the value of a bind_data column captured in v$sql this function returns the collection (list) of bind values associated to the corresponding SQL statement dbms_sqltune_util0.extract_binds(bind_data IN RAW) RETURN SQL_BIND_SET PIPELINED;

desc sys.sql_bind_set

      

      See GET_BINDS_COUNT Demo Below

GET_BINDS_COUNT

Given the value of a bind_data column in raw type this function returns the number of bind values contained in the column dbms_sqltune_util0.get_binds_count(bind_data IN RAW) RETURN PLS_INTEGER;

DECLARE

       bdata  RAW(2000);

       retVal PLS_INTEGER;

       x      VARCHAR2(30);

       y      VARCHAR2(40);

       z      DATE;

       sb     sys.sql_bind;

      BEGIN

        SELECT bind_data

        INTO bdata

        FROM v$sql

        WHERE is_bind_sensitive = 'Y'

        AND rownum = 1;

        dbms_output.put_line(bdata);

      

        retVal := dbms_sqltune_util0.get_binds_count(bdata);

        dbms_output.put_line(TO_CHAR(retVal));

      

        SELECT datatype_string, value_string, last_captured

        INTO x, y, z

        FROM TABLE(dbms_sqltune_util0.extract_binds(bdata));

        dbms_output.put_line('Data Type:  ' || x);

        dbms_output.put_line('Val String: ' || y);

        dbms_output.put_line('Last Capt:  ' || TO_CHAR(z));

      

        sb := dbms_sqltune_util0.extract_bind(bdata, 1);

        dbms_output.put_line('Val String: ' || sb.value_string);

      END;

      /

GET_DB_LINK_TO_PRIM

Returns a database link to a primary database dbms_sqltune_util0.get_db_link_to_prim RETURN VARCHAR2;

SELECT dbms_sqltune_util0.get_db_link_to_prim

      FROM dual;

GET_SUBST_QUERY

Returns the substituted query for the given original query dbms_sqltune_util0.get_subst_query( orig_qry IN VARCHAR2, subst_qry OUT VARCHAR2);

DECLARE

       outVal VARCHAR2(240);

      BEGIN

        dbms_sqltune_util0.get_subst_query('SELECT COUNT(*) FROM tab$', outVal);

        dbms_output.put_line(outVal);

      END;

      /

IS_BIND_MASKED

Examines a flag to determine if a bind at a given pos is masked dbms_sqltune_util0.is_bind_masked( bind_pos IN PLS_INTEGER, masked_binds_flag IN RAW DEFAULT NULL) RETURN NUMBER;

TBD

SET_DB_LINK_TO_PRIM

Sets the database link to the primary database dbms_sqltune_util0.set_db_link_to_prim(db_link_to IN VARCHAR2);

exec dbms_sqltune_util0.set_db_link_to_prim('UWDBLINK');

SQLTEXT_TO_SIGNATURE

Returns a sql text's signature which can be used to identify the sql text in dba_sql_profiles dbms_sqltune_util0.sqltext_to_signature( sql_text IN CLOB, force_match IN BINARY_INTEGER := 0) RETURN NUMBER;

DECLARE

       new_sig NUMBER;

       sqltext CLOB := 'SELECT dummy FROM dual';

      BEGIN

        new_sig := sys.dbms_sqltune_util0.sqltext_to_signature(sqltext, 0);

        dbms_output.put_line(TO_CHAR(new_sig));

      END;

      /

      

      -- another example can be found in {ORACLE_HOME}/rdbms/admin/a1001000.sql

SQLTEXT_TO_SQLID

Returns a sql text's id which can be used to identify sql text in v$sqlXXX views dbms_sqltune_util0.sqltext_to_sqlid(sql_text IN CLOB) RETURN VARCHAR2;

DECLARE

       retVal  NUMBER;

       sqlid   v$sql_plan.sql_id%TYPE;

       sqltext CLOB := 'SELECT dummy FROM dual';

      BEGIN

        sqlid := sys.dbms_sqltune_util0.sqltext_to_sqlid(sqltext);

        dbms_output.put_line(sqlid);

        retVal := sys.dbms_sqltune_util0.validate_sqlid(sqlid);

        dbms_output.put_line(retVal);

      END;

      /

VALIDATE_SQLID

Validates a client sql id by converting it to a ub8 and back and checking to make sure there is no change Returns 1 if valid, else 0. dbms_sqltune_util0.validate_sqlid(sql_id IN VARCHAR2) RETURN BINARY_INTEGER;

See SQLTEXT_TO_SQLID Demo Above