Oracle DBMS_SQLTUNE_UTIL0
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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_LIB PRVT_ADVISOR
DBMS_PERF DBMS_SQLTUNE_UTIL1 PRVT_AWRV_METADATA
DBMS_SMB DBMS_STANDARD PRVT_AWR_VIEWER
DBMS_SQLDIAG DBMS_SYS_ERROR PRVT_REPORT_TAGS
DBMS_SQLPA DBMS_XPLAN PRVT_SQLADV_INFRA
DBMS_SQLTCB_INTERNAL PRVTEMX_DBHOME SQL_BIND
DBMS_SQLTUNE PRVTEMX_PERF SQL_BIND_SET
DBMS_SQLTUNE_INTERNAL PRVTEMX_RSRCMGR WRI$_ADV_SQLTUNE
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 10gR1
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/dbmssqlu.sql
Subprograms
 
ADD_SUBST_PATTERN (new 12.2)
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_NAME2IDS
Returns a container dbid and container id given a container name 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;
/
 
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_OBJ_PRIV (new 12.2)
Checks whether a user has SELECT privilege on an object

The problem, however, is that it returns 1 even when the schema being checked does not have the SELECT privilege on the table in question. That it is broken makes perfect sense when you realize it must be run by SYS because no privs are granted ... and ... the user_name being checked is nullable. About the only way I would expect this to work would be with Database Vault where SYS might not have privs to a schema.
dbms_sqltune_util0.check_obj_priv(
user_name    IN VARCHAR2 := NULL,
object_owner IN VARCHAR2 := NULL,
object_name  IN VARCHAR2,
object_type  IN VARCHAR2 := NULL)
RETURN BINARY_INTEGER;
DECLARE
 retVal PLS_INTEGER;
BEGIN
  retVal := dbms_sqltune_util0.check_obj_priv('UWCLASS', 'UWCLASS', 'SERVERS', 'TABLE');
  dbms_output.put_line(retVal);
END;
/
1

DECLARE
 retVal PLS_INTEGER;
BEGIN
  retVal := dbms_sqltune_util0.check_obj_priv('DGSYS', 'UWCLASS', 'SERVERS', 'TABLE');
  dbms_output.put_line(retVal);
END;
/
 
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 (new 12.2)
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 (new 12.2)
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
 
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

Related Topics
Built-in Functions
Built-in Packages
DBMS_AUTO_SQLTUNE
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLSET
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2
PRVTEMX_PERF
What's New In 12cR1
What's New In 12cR2

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