Oracle DBMS_WRR_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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