Oracle DBMS_SQLTUNE_UTIL2
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 11.2.0.3 to 12.1.0.1. 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.
Purpose This package is for shared utility functions that need to be part of an INVOKER rights package. Like the other dbms_sqltune_utilX packages, it should NOT be documented. If a function only needs to be accessible from the dbms_sqltune/sqldiag/etc feature layer, do not put it here, but rather in the infrastructure layer (prvssqlf). This layer is for code that should be globally accessible, even from the internal package.
AUTHID CURRENT_USER
Dependencies
DBA_HIST_SNAPSHOT DBMS_SQLTUNE_INTERNAL PRVT_SQLSET_INFRA
DBMS_ADVISOR DBMS_SQLTUNE_LIB SQL_BIND
DBMS_AUTO_REPORT DBMS_SQLTUNE_UTIL1 SQL_BINDS
DBMS_AUTO_REPORT_INTERNAL DBMS_STANDARD SQL_BIND_SET
DBMS_SMB DBMS_SYS_ERROR SYSTEM_PRIVILEGE_MAP
DBMS_SPM_INTERNAL DBMS_UTILITY V$DATABASE
DBMS_SQLDIAG PLITBLM WRI$_ADV_EXECUTIONS
DBMS_SQLPA PRVT_ADVISOR WRI$_ADV_TASKS
DBMS_SQLTUNE PRVT_SQLADV_INFRA  
Documented No
Exceptions
Error Code Reason
ORA-13768 Snapshot ID must be between <beginning snap_id> and <max_possible_valid_snap_id>;
   
First Available 10gR1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmssqlu.sql
Subprograms
 
CHECK_PRIV
Does a callout into the kernel to check for the given system privilege. It returns TRUE or FALSE based on whether the current user has the privilege enabled. dbms_sqltune_util2.check_priv(priv IN VARCHAR2) RETURN BOOLEAN;
conn / as sysdba
BEGIN
  IF dbms_sqltune_util2.check_priv('SYSDBA') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
GET_TIMING_INFO (new 12.1)
Returns elapsed and CPU timing information for a section of PL/SQL code dbms_sqltune_util2.get_timing_info(
phase   IN     BINARY_INTEGER,
elapsed IN OUT NUMBER,
cpu     IN OUT NUMBER);
TBD
 
RESOLVE_EXEC_NAME
Validates the execution name of an SPA task to ensure it was a Compare Performance (type id 5) while if NULL was supplied,it returns the name of the most recent compare execution for the given SPA task dbms_sqltune_util2.resolve_exec_name(
task_name  IN     VARCHAR2,
task_owner IN     VARCHAR2,
exec_name  IN OUT VARCHAR2)
RETURN NUMBER;
TBD
 
RESOLVE_USERNAME
When passed a NULL name, this function returns the current schema owner. Otherwise, it returns the name passed in, after validating it. dbms_sqltune_util2.resolve_username(
user_name IN VARCHAR2,
validate  IN BOOLEAN := TRUE,
con_id    IN NUMBER  := NULL)
RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

DECLARE
 retVal VARCHAR2(30);
BEGIN
  retVal := dbms_sqltune_util2.resolve_username(USER);
  dbms_output.put_line(retVal);
END;
/
 
SQL_BINDS_NTAB_TO_VARRAY
Converts the sql binds data from the nested table stored in the staging table on an unpack/pack to the varray type used in the SQLSET_ROW dbms_sqltune_util2.sql_binds_ntab_to_varray(
binds_ntab IN SQL_BIND_SET)
RETURN SQL_BINDS;
TBD
 
SQL_BINDS_VARRAY_TO_NTAB
Converts the sql binds data from a VARRAY as it exists in SQLSET_ROW into a nested table that can be stored in the staging table dbms_sqltune_util2.sql_binds_varray_to_ntab(
binds_varray IN SQL_BINDS)
RETURN SQL_BIND_SET;
TBD
 
VALIDATE_SNAPSHOT
Checks whether a snapshot id interval is valid. It raises an error if passed an invalid interval. dbms_sqltune_util2.validate_snapshot(
begin_snap IN NUMBER,
end_snap   IN NUMBER,
incl_bid   IN BOOLEAN := FALSE);
SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT, s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

exec dbms_sqltune_util2.validate_snapshot(2376, 2391);

SELECT max(snap_id)+100
FROM dba_hist_snapshot;

exec dbms_sqltune_util2.validate_snapshot(2376, 9999);

Related Topics
DBMS_SQLDIAG
DBMS_SQLPA
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL1
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