Oracle DBMS_WRR_INTERNAL
Version 12.1.0.2

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 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$SYSSTAT
DBA_HIST_ACTIVE_SESS_HISTORY DBMS_SWRF_INTERNAL V$SYSTEM_PARAMETER
DBA_HIST_PARAMETER DBMS_SYSTEM V$SYS_TIME_MODEL
DBA_HIST_SNAPSHOT DBMS_UTILITY WRI$_ALERT_HISTORY
DBA_SQLSET DBMS_WORKLOAD_CAPTURE WRR$_CAPTURES
DBA_WORKLOAD_CAPTURES DBMS_WORKLOAD_CAPTURE_LIB WRR$_CAPTURE_ID
DBA_WORKLOAD_REPLAYS DBMS_WORKLOAD_REPLAY WRR$_CAPTURE_STATS
DBA_WORKLOAD_REPLAY_DIVERGENCE DBMS_WORKLOAD_REPOSITORY WRR$_CONNECTION_MAP
DBA_WORKLOAD_SCHEDULE_CAPTURES DUAL WRR$_FILTERS
DBMS_ADVISOR KU$_DUMPFILE_INFO WRR$_REPLAYS
DBMS_ASH_INTERNAL KU$_DUMPFILE_ITEM WRR$_REPLAY_DIVERGENCE
DBMS_ASSERT PLITBLM WRR$_REPLAY_ID
DBMS_DATAPUMP PRVT_REPORT_TAGS WRR$_REPLAY_STATS
DBMS_LOB PRVT_SMGUTIL WRR$_SCHEDULE_ORDERING
DBMS_RANDOM SYS_IXMLAGG WRR$_USER_MAP
DBMS_RAT_MASK USER_USERS WRR$_WORKLOAD_ATTRIBUTES
DBMS_REPORT UTL_FILE XMLAGG
DBMS_SCHEDULER V$ACTIVE_SESSION_HISTORY XMLSEQUENCE
DBMS_SQLPA V$DATABASE XMLTYPE
DBMS_SQLTUNE    
Documented No
Exceptions
Error Code Reason
ORA-20333 Bad string "<string>" to represent a boolean value
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 END_REPLAY_ACTIONS INVOKE_ACQUIRE_WRR_LOCK
ADD_CAPTURE EXPORT_STS INVOKE_CHECK_SQLSET_PRIVS
ADD_CAPTURE_STATS EXPORT_STS_FROM_CAPTURE INVOKE_DIS_RES_SESS`
ADD_FILTER (2) EXPORT_STS_FROM_REPLAY INVOKE_GET_PATH
ADD_REPLAY EXPORT_UC_GRAPH INVOKE_KGHSFSNEWFILE
ADD_REPLAY_STATS FILE_EXISTS INVOKE_READ_WMD
ADJUST_TIMES_TO_SNAP_TIMEZONE FINALIZE_STS_CAPTURE INVOKE_RELEASE_WRR_LOCK
BUILD_CAPTURE_INFO_TAG FORMAT_BYTES INVOKE_UPDATE_WMD
CAPTURE_EXPORT_AWR FORMAT_INTERVAL IS_CONSOLIDATED_DATABASE
CAPTURE_UPDATE_EXP_STATUS GENERATE_CAPTURE_WID IS_CONTAINER_CDBROOT
CHECK_RAT_EXEC_OPTION GET_ASH_REPORT_REFERENCE IS_CONTAINER_PDB
COMPARE_STS GET_BOOLEAN_VALUE LOAD_WORKLOAD_ATTRIBUTES
COPY_FILTERS GET_CAPINFO_INTERNAL OS_SECONDS
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
CREATE_SNAPSHOT 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 GET_UNIQUE_DBID TRUNCATE_TABLE
DELETE_FILTER IMPORT_STS UPDATE_CAPTURE_TOTAL_STATS
DELETE_REPLAY IMPORT_STS_FROM_CAPTURE VALID_DEFAULT_ACTION
DROP_DIR_OBJ IMPORT_STS_FROM_REPLAY WI_TRANSFORM_ID_TO_STR
DROP_TABLE IMPORT_UC_GRAPH  
 
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_BOOLEAN_VALUE
Given an input of the string TRUE, T, '1', FALSE, F or '0' returns the BOOLEAN representation dbms_wrr_internal.get_boolean_value(para IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_wrr_internal.get_boolean_value('0') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Invalid String');
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;
 
GET_UNIQUE_DBID
Random DBID number generator dbms_wrr_internal.get_unique_dbid RETURN NUMBER;
SELECT dbms_wrr_internal.get_unique_dbid
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_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