Oracle DBMS_SQLTUNE_UTIL0
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
Dependencies
DBMS_PERF DBMS_SQLTUNE_INTERNAL PRVT_AWR_VIEWER
DBMS_SMB DBMS_SQLTUNE_LIB PRVT_REPORT_TAGS
DBMS_SQLDIAG DBMS_SQLTUNE_UTIL1 PRVT_SQLADV_INFRA
DBMS_SQLPA DBMS_XPLAN SQL_BIND
DBMS_SQLTCB_INTERNAL PRVTEMX_DBHOME SQL_BIND_SET
DBMS_SQLTUNE PRVTEMX_PERF  
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
 
CDBCON_DBID_TO_NAME (new 12.1)
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 (new 12.1)
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  (new 12.1)
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 (new 12.1)
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 (new 12.1)
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
 
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;
/
 
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 whiche 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
DBMS_AUTO_SQLTUNE
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2
PRVTEMX_PERF
Packages

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