Oracle DBMS_WRR_INTERNAL
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 Internal utilities supporting Workload Capture and Replay. This page is capturing a small number of demos related to things I found of interest.
AUTHID DEFINER
Data Types TYPE natural_array ...;
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 DROP_TABLE INIT_CAPTURE_STATS
ADD_CAPTURE END_REPLAY_ACTIONS INVOKE_ACQUIRE_WRR_LOCK
ADD_CAPTURE_BUCKET EXPORT_REP_EXT_TBL INVOKE_CHECK_SQLSET_PRIVS
ADD_CAPTURE_STATS EXPORT_STS INVOKE_DIS_RES_SESS
ADD_FILTER (2) EXPORT_STS_FROM_CAPTURE INVOKE_GET_FEATURE_BOOL_VALUE
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
ARG_MIN FINALIZE_STS_CAPTURE INVOKE_UPDATE_WMD
BITOR FORMAT_BYTES IS_CONSOLIDATED_DATABASE
BOOL_TO_STRING FORMAT_INTERVAL IS_CONTAINER_CDBROOT
BUILD_CAPTURE_INFO_TAG GENERATE_CAPTURE_WID IS_CONTAINER_PDB
CAPTURE_EXPORT_AWR GET_ASH_REPORT_REFERENCE LIST_TO_TABLE
CAPTURE_UPDATE_EXP_STATUS GET_BOOLEAN_VALUE LIST_TO_TABLE_INT
CHECK_PLSQL_MODE_VALID GET_CAPINFO_INTERNAL LOAD_WORKLOAD_ATTRIBUTES
CHECK_RAT_EXEC_OPTION GET_CAPTURE_SIG OS_SECONDS
COMPARE_STS GET_DIR_PATH PUT_FILE
COPY_FILTERS GET_EXPORT_STATUS_I REPLAY_EXPORT_AWR
COUNT_FILTERS GET_FILE REPLAY_REPORT_INTERNAL
CREATE_DIR_OBJ GET_PLSQL_MODE_INTERNAL REPLAY_SUFFIX
CREATE_DIR_OBJ_TMP GET_REPLAY_SIG SAVE_REPLAY_THREAD
CREATE_SNAPSHOT GET_ROW_DIVERGENCE_BCK_HLP START_STS_CAPTURE
DBG_TRACE GET_SNAP_ASH_TIMES STOP_SQL_SET_CAPTURE
DB_DATE GET_STS_NAME TO_DBTZ
DELETE_CAPTURE GET_UNIQUE_DBID TRUNCATE_TABLE
DELETE_FILE IMPORT_STS UPDATE_CAPTURE_TOTAL_STATS
DELETE_FILTER IMPORT_STS_FROM_CAPTURE UPDATE_PROPS
DELETE_REPLAY IMPORT_STS_FROM_REPLAY VALID_DEFAULT_ACTION
DROP_DIR_OBJ IMPORT_UC_GRAPH WI_TRANSFORM_ID_TO_STR
DROP_INDEX    
 
ADD_CAPTURE_BUCKET (new 12.2)
Undocumented dbms_wrr_internal.add_capture_bucket(
rec_id    IN NUMBER,
bucket_id IN NUMBER);
exec dbms_wrr_internal.add_capture_bucket(1,1);
 
ARG_MIN (new 12.2)
Undocumented dbms_wrr_internal.arg_min(arr IN dbms_utility.index_table_type)
RETURN BINARY_INTEGER;
TBD
 
BITOR (new 12.2)
Undocumented dbms_wrr_internal.bitor(
curval IN NUMBER,
bits   IN NUMBER)
RETURN NUMBER;
SQL> SELECT dbms_wrr_internal.bitor(20, 9)
  2  FROM dual;

DBMS_WRR_INTERNAL.BITOR(20,9)
-----------------------------
                           29

SQL> SELECT dbms_wrr_internal.bitor(20, -9)
  2  FROM dual;

DBMS_WRR_INTERNAL.BITOR(20,-9)
------------------------------
                            -9
 
BOOL_TO_STRING (new 12.2)
Returns 'Y' if TRUE and 'N' if FALSE dbms_wrr_internal.bool_to_string(para IN BOOLEAN) RETURN VARCHAR2;
BEGIN
  dbms_output.put_line(dbms_wrr_internal.bool_to_string(TRUE));
  dbms_output.put_line(dbms_wrr_internal.bool_to_string(FALSE));
END;
/
 
CHECK_PLSQL_MODE_VALID (new 12.2)
Undocumented dbms_wrr_internal.check_plsql_mode_valid(
capture_id IN NUMBER,
pmode      IN BINARY_INTEGER,
errmsg     IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
CHECK_RAT_EXEC_OPTION
Returns the database date which may not be the same as the value returned by SYSDATE dbms_wrr_internal.check_rat_exec_option;
exec dbms_wrr_internal.check_rat_exec_option;
 
CREATE_DIR_OBJ
Creates a directory defined by its name and path dbms_wrr_internal.create_dir_obj(
dobj  IN VARCHAR2,
dpath IN VARCHAR2);
SQL> exec dbms_wrr_internal.create_dir_obj('WRR_DIR', 'C:\TEMP');

PL/SQL procedure successfully completed.

SQL> drop directory wrr_dir;

Directory dropped.
 
CREATE_SNAPSHOT
Creates an AWR snapshot on demand. Similar functionality to DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT dbms_wrr_internal.create_snapshot(retry_on_failure IN BOOLEAN) RETURN NUMBER;
SQL> BEGIN
  2    dbms_output.put_line(dbms_wrr_internal.create_snapshot(TRUE));
  3   END;
  4   /
1831

PL/SQL procedure successfully completed.
 
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_INDEX (new 12.2)
Drops the specified index dbms_wrr_internal.drop_index(idxname IN VARCHAR2);
SQL> CREATE TABLE t (
  2  testcol NUMBER);

Table created.

SQL> CREATE INDEX ix_t
  2  ON t(testcol);

Index created.

SQL> exec dbms_wrr_internal.drop_index('IX_T');

PL/SQL procedure successfully completed.

SQL> SELECT index_name
  2  FROM dba_indexes
  3* WHERE table_name = 'T';

no rows selected
 
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');
 
EXPORT_REP_EXT_TBL (new 12.2)
Undocumented dbms_wrr_internal.export_rep_ext_tbl(
rep_id   IN NUMBER,
dir_path IN VARCHAR2,
perm_tbl IN VARCHAR2);
TDB
 
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;
/
 
FORMAT_BYTES
Suffixes a numeric value with the word "bytes" dbms_wrr_internal.format_bytes(bytes IN NUMBER) RETURN VARCHAR2;
SQL> SELECT dbms_wrr_internal.format_bytes(0.7)
  2  FROM dual;

DBMS_WRR_INTERNAL.FORMAT_BYTES(.7)
-----------------------------------
.7 bytes
 
FORMAT_INTERVAL
Formats a numeric value as an interval is days, hours, minutes, and seconds dbms_wrr_internal.format_interval(days IN NUMBER) RETURN VARCHAR2;
SQL> SELECT dbms_wrr_internal.format_interval(3.2547)
  2  FROM dual;

DBMS_WRR_INTERNAL.FORMAT_INTERVAL(3.2547)
------------------------------------------
3 days 6 hours 6 minutes 46.8 seconds
 
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_PLSQL_MODE_INTERNAL (new 12.2)
Do not know what mode values are valid. The function returned 3 for every string I tried ... even nonsense strings. dbms_wrr_internal.get_plsql_mode_internal(plsql_mode IN VARCHAR2)
RETURN BINARY_INTEGER;
SQL> SELECT dbms_wrr_internal.get_plsql_mode_internal('NATIVE')
  2  FROM dual;

DBMS_WRR_INTERNAL.GET_PLSQL_MODE_INTERNAL('NATIVE')
---------------------------------------------------
                                                  3
 
GET_SNAP_ASH_TIMES (new 12.2)
Returns the ASH times corresponding with an AWR snap dbms_wrr_internal.get_snap_ash_times(
awrbsnap IN  NUMBER,
awresnap IN  NUMBER,
dbid     IN  NUMBER,
stime    OUT DATE,
etime    OUT DATE);
SQL> SELECT dbid FROM v$database;

      DBID
----------
 549318987

-- retrieve snap_ids from the following query
SELECT TO_CHAR(s.startup_time) INST_START,
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;

DECLARE
 stime DATE;
 etime DATE;
BEGIN
  dbms_wrr_internal.get_snap_ash_times(1820, 1822, 549318987, stime, etime);
  dbms_output.put_line(TO_CHAR(stime));
  dbms_output.put_line(TO_CHAR(etime));
END;
/
 
GET_UNIQUE_DBID
Random DBID number generator dbms_wrr_internal.get_unique_dbid RETURN NUMBER;
SELECT dbms_wrr_internal.get_unique_dbid
FROM dual;

/
 
INIT_CAPTURE_STATS (new 12.2)
Undocumented dbms_wrr_internal.init_capture_stats(
recid     IN NUMBER,
instid    IN NUMBER,
stime     IN DATE,
hostname  IN VARCHAR2,
par       IN VARCHAR2,
dbtm      IN NUMBER,
usercalls IN NUMBER,
numtxn    IN NUMBER,
numconn   IN NUMBER);
TBD
 
INVOKE_CHECK_SQLSET_PRIVS
Undocumented dbms_wrr_internal.invoke_check_sqlset_privs;
exec dbms_wrr_internal.invoke_check_sqlset_privs;
 
INVOKE_DIS_RES_SESS
Undocumented dbms_wrr_internal.invoke_dis_res_sess;
exec dbms_wrr_internal.invoke_dis_res_sess;
 
INVOKE_GET_FEATURE_BOOL_VALUE (new 12.2)
Appears that it should return TRUE if a feature is invoked but as this is an undocumented function there is no list of valid feature values dbms_wrr_internal.invoke_get_feature_bool_value(feature_name IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_wrr_internal.invoke_get_feature_bool_value('RAC') THEN
    dbms_output.put_line('Invoked');
  ELSE
    dbms_output.put_line('Not Invoked');
  END IF;
END;
/
 
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;
/
 
LIST_TO_TABLE (new 12.2)
Overload 1 dbms_wrr_internal.list_to_table(
list       IN     VARCHAR2,
tab           OUT dbms_utility.lname_array,
separator  IN     CHAR,
sort       IN     BOOLEAN)
RETURN BINARY_INTEGER;
TBD
Overload 2 dbms_wrr_internal.list_to_table
list       IN     VARCHAR2,
tab           OUT dbms_wrr_internal.natural_array,
separator  IN     CHAR,
sort       IN     BOOLEAN)
RETURN BINARY_INTEGER;
TBD
 
LIST_TO_TABLE_INT (new 12.2)
Undocumented dbms_wrr_internal.list_to_table_int(
list       IN     VARCHAR2,
tab           OUT dbms_utility.lname_array,
separator  IN     CHAR,
trim_words IN     BOOLEAN)
RETURN BINARY_INTEGER;
TBD
 
OS_SECONDS
Returns the number of seconds on the operating system dbms_wrr_internal.os_seconds RETURN BINARY_INTEGER;
SQL> SELECT dbms_wrr_internal.os_seconds
  2  FROM dual;

OS_SECONDS
----------
1510445432
 
SAVE_REPLAY_THREAD (new 12.2)
Undocumented dbms_wrr_internal.save_replay_thread(
rep_id  IN NUMBER,
file_id IN NUMBER);
TBD
 
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;
 
UPDATE_PROPS (new 12.2)
Undocumented dbms_wrr_internal.update_props(
prop_name  IN VARCHAR2,
prop_value IN VARCHAR2);
TBD

Related Topics
AS_REPLAY
Built-in Functions
Built-in Packages
DBMS_APP_CONT
DBMS_APP_CONT_PRVT
DBMS_UTILITY
DBMS_WORKLOAD_CAPTURE
DBMS_WORKLOAD_REPLAY
DBMS_WORKLOAD_REPOSITORY
Directories
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