Oracle DBMS_WRR_INTERNAL
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 Internal utilities supporting Workload Capture and Replay. This page is capturing a small number of demos related to things I found of interest while Beta testing 12cR1.
AUTHID DEFINER
Data Types SYS.XMLTYPE
Dependencies
DBA_DIRECTORIES DBMS_STANDARD V$SYS_TIME_MODEL
DBA_HIST_ACTIVE_SESS_HISTORY DBMS_SWRF_INTERNAL WRI$_ALERT_HISTORY
DBA_HIST_PARAMETER DBMS_SYSTEM WRR$_CAPTURES
DBA_SQLSET DBMS_UTILITY WRR$_CAPTURE_ID
DBA_WORKLOAD_CAPTURES DBMS_WORKLOAD_CAPTURE WRR$_CAPTURE_STATS
DBA_WORKLOAD_REPLAYS DBMS_WORKLOAD_CAPTURE_LIB WRR$_CONNECTION_MAP
DBA_WORKLOAD_REPLAY_DIVERGENCE DBMS_WORKLOAD_REPLAY WRR$_FILTERS
DBA_WORKLOAD_SCHEDULE_CAPTURES DUAL WRR$_REPLAYS
DBMS_ADVISOR KU$_DUMPFILE_INFO WRR$_REPLAY_DIVERGENCE
DBMS_ASH_INTERNAL KU$_DUMPFILE_ITEM WRR$_REPLAY_ID
DBMS_ASSERT PLITBLM WRR$_REPLAY_STATS
DBMS_DATAPUMP PRVT_REPORT_TAGS WRR$_SCHEDULE_ORDERING
DBMS_LOB PRVT_SMGUTIL WRR$_USER_MAP
DBMS_RANDOM SYS_IXMLAGG WRR$_WORKLOAD_ATTRIBUTES
DBMS_RAT_MASK USER_USERS XMLAGG
DBMS_REPORT UTL_FILE XMLSEQUENCE
DBMS_SCHEDULER V$SYSSTAT XMLSEQUENCETYPE
DBMS_SQLPA V$SYSTEM_PARAMETER XMLTYPE
DBMS_SQLTUNE    
Documented No
First Available 12.1.0
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtwrr.plb
Subprograms
ACQUIRE_WRR_LOCK DROP_TABLE INVOKE_ACQUIRE_WRR_LOCK
ADD_CAPTURE END_REPLAY_ACTIONS INVOKE_CHECK_SQLSET_PRIVS
ADD_CAPTURE_STATS EXPORT_STS INVOKE_DIS_RES_SESS
ADD_FILTER (2) EXPORT_STS_FROM_CAPTURE INVOKE_GET_PATH
ADD_REPLAY EXPORT_STS_FROM_REPLAY INVOKE_KGHSFSNEWFILE
ADD_REPLAY_STATS EXPORT_UC_GRAPH INVOKE_READ_WMD
ADJUST_TIMES_TO_SNAP_TIMEZONE FILE_EXISTS INVOKE_RELEASE_WRR_LOCK
BUILD_CAPTURE_INFO_TAG FINALIZE_STS_CAPTURE INVOKE_UPDATE_WMD
CAPTURE_EXPORT_AWR FORMAT_BYTES IS_CONSOLIDATED_DATABASE
CAPTURE_UPDATE_EXP_STATUS FORMAT_INTERVAL IS_CONSOLIDATED_DIR
CHECK_RAT_EXEC_OPTION GENERATE_CAPTURE_WID IS_CONTAINER_CDBROOT
COMPARE_STS GET_ASH_REPORT_REFERENCE IS_CONTAINER_PDB
COPY_FILTERS GET_CAPINFO_INTERNAL LOAD_WORKLOAD_ATTRIBUTES
COUNT_FILTERS GET_CAPTURE_SIG PUT_FILE
CREATE_DIR_OBJ GET_DIR_PATH REPLAY_EXPORT_AWR
CREATE_DIR_OBJ_TMP GET_EXPORT_STATUS_I REPLAY_REPORT_INTERNAL
DATE_TO_EPOCH_SECS GET_FILE REPLAY_SUFFIX
DBG_TRACE GET_REPLAY_SIG START_STS_CAPTURE
DB_DATE GET_ROW_DIVERGENCE_BCK_HLP STOP_SQL_SET_CAPTURE
DELETE_CAPTURE GET_STS_NAME TO_DBTZ
DELETE_FILE IMPORT_STS TRUNCATE_TABLE
DELETE_FILTER IMPORT_STS_FROM_CAPTURE UPDATE_CAPTURE_TOTAL_STATS
DELETE_REPLAY IMPORT_STS_FROM_REPLAY VALID_DEFAULT_ACTION
DROP_DIR_OBJ IMPORT_UC_GRAPH WI_TRANSFORM_ID_TO_STR
 
DB_DATE
Returns the database date which may not be the same as the value returned by SYSDATE dbms_wrr_internal.db_date RETURN DATE;
SELECT  dbms_wrr_internal.db_date
FROM dual;

SELECT sysdate
FROM dual;
 
DROP_DIR_OBJ
Drops a directory object dbms_wrr_internal.drop_dir_obj(dobj IN VARCHAR2);
CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

col directory_name format a30
col directory_path format a50

SELECT *
FROM dba_directories
ORDER BY 2;

exec dbms_wrr_internal.drop_dir_obj('CTEMP');

SELECT *
FROM dba_directories
ORDER BY 2;
 
DROP_TABLE
Drops a table dbms_wrr_internal.drop_table(tbname IN VARCHAR2);
CREATE TABLE test AS
SELECT * FROM tab$;

exec dbms_wrr_internal.drop_table('SYS.TEST');
 
FILE_EXISTS
Returns TRUE if a file exists, Otherwise FALSE dbms_wrr_internal.file_exists(
dir  IN VARCHAR2,
file IN VARCHAR2)
RETURN BOOLEAN;
CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

-- create a file named test.txt in the CTEMP directory

BEGIN
  IF dbms_wrr_internal.file_exists('CTEMP', 'TEST.TXT') THEN
    dbms_output.put_line('File Found');
  ELSE
    dbms_output.put_line('File Not Found');
  END IF;
END;
/
 
GET_DIR_PATH
Returns the full operating system path corresponding to a directory object name dbms_wrr_internal.get_dir_path(dir_obj_i IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_wrr_internal.get_dir_path('XMLDIR')
FROM dual;
 
IS_CONSOLIDATED_DATABASE
Returns TRUE in the 12cR1 Beta 2 installed as a container DB dbms_wrr_internal.is_consolidated_database RETURN BOOLEAN;
BEGIN
  IF dbms_wrr_internal.is_consolidated_database THEN
    dbms_output.put_line('Is a Consolidated Database');
  ELSE
    dbms_output.put_line('Is not a Consolidated Database');
  END IF;
END;
/
 
IS_CONSOLIDATED_DIR
Failing a clear definition of what is a consolidated directory object all I can say is that the code at right appears to work dbms_wrr_internal.is_consolidated_dir(dir_obj IN VARCHAR2) RETURN BOOLEAN;
SELECT directory_name
FROM dba_directories;

BEGIN
  IF dbms_wrr_internal.is_consolidated_dir('XMLDIR') THEN
    dbms_output.put_line('Is Consolidated Directory Object');
  ELSE
    dbms_output.put_line('Is not a Consolidated Directory Object');
  END IF;
END;
/
 
IS_CONTAINER_CDBROOT
Returns TRUE if connect to CDB$ROOT dbms_wrr_internal.is_container_cdbroot RETURN BOOLEAN;
BEGIN
  IF dbms_wrr_internal.is_container_cdbroot THEN
    dbms_output.put_line('Is CDB$ROOT');
  ELSE
    dbms_output.put_line('Is not CDB$ROOT');
  END IF;
END;
/
 
IS_CONTAINER_PDB
Returns FALSE when connected to CDB$ROOT which makes no sense but then this is beta. dbms_wrr_internal.is_container_pdb RETURN BOOLEAN;
BEGIN
  IF dbms_wrr_internal.is_container_pdb THEN
    dbms_output.put_line('Is a container DB');
  ELSE
    dbms_output.put_line('Is not a container DB');
  END IF;
END;
/
 
TO_DBTZ
Undocumented dbms_wrr_internal.to_dbtz(sys_dt IN DATE) RETURN DATE;
SELECT sysdate, dbms_wrr_internal.to_dbtz(sysdate-1)
FROM dual;
 
TRUNCATE_TABLE
Truncates a table with a procedure call dbms_wrr_internal.truncate_table(tbname IN VARCHAR2);
CREATE TABLE test AS
SELECT * FROM tab$;

SELECT COUNT(*)
FROM test;

exec dbms_wrr_internal.truncate_table('TEST');

SELECT COUNT(*)
FROM test;

Related Topics
AS_REPLAY
DBMS_APP_CONT
DBMS_APP_CONT_PRVT
DBMS_WORKLOAD_CAPTURE
DBMS_WORKLOAD_REPLAY
Directories
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