Oracle DBMS_WRR_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
AS_REPLAY DBMS_SYSTEM WRR$_CAPTURES
AWR_PDB_ACTIVE_SESS_HISTORY DBMS_UTILITY WRR$_CAPTURE_BUCKETS
DBA_DIRECTORIES DBMS_WORKLOAD_CAPTURE WRR$_CAPTURE_FILES
DBA_HIST_ACTIVE_SESS_HISTORY DBMS_WORKLOAD_CAPTURE_I WRR$_CAPTURE_ID
DBA_HIST_PARAMETER DBMS_WORKLOAD_CAPTURE_LIB WRR$_CAPTURE_LONG_SQLTEXT
DBA_HIST_SNAPSHOT DBMS_WORKLOAD_REPLAY WRR$_CAPTURE_SCHEMA_INFO
DBA_SQLSET DBMS_WORKLOAD_REPLAY_I WRR$_CAPTURE_SCHEMA_INFO_TMP
DBA_WORKLOAD_CAPTURES DBMS_WORKLOAD_REPLAY_LIB WRR$_CAPTURE_SQLTEXT
DBA_WORKLOAD_DIV_SUMMARY DBMS_WORKLOAD_REPOSITORY WRR$_CAPTURE_SQL_TMP
DBA_WORKLOAD_REPLAYS DBMS_WRR_PROTECTED WRR$_CAPTURE_STATS
DBA_WORKLOAD_REPLAY_DIVERGENCE DUAL WRR$_CONNECTION_MAP
DBA_WORKLOAD_REPLAY_THREAD GV$ACTIVE_SESSION_HISTORY WRR$_FILTERS
DBA_WORKLOAD_SCHEDULE_CAPTURES GV$INSTANCE WRR$_REPLAYS
DBMS_ADVISOR GV$SESSION WRR$_REPLAY_DIVERGENCE
DBMS_ASH_INTERNAL GV$SQL WRR$_REPLAY_DIV_SUMMARY
DBMS_ASSERT GV$WORKLOAD_REPLAY_THREAD WRR$_REPLAY_FILES
DBMS_DATAPUMP KU$_DUMPFILE_INFO WRR$_REPLAY_ID
DBMS_LOB KU$_DUMPFILE_ITEM WRR$_REPLAY_IFSLA
DBMS_RANDOM PLITBLM WRR$_REPLAY_LOGIN_QUEUE
DBMS_RAT_MASK_INTERNAL PROPS$ WRR$_REPLAY_STATS
DBMS_REPLAYHUB PRVT_REPORT_TAGS WRR$_REPLAY_TRACKED_COMMITS
DBMS_REPORT SYS_IXMLAGG WRR$_SCHEDULE_ORDERING
DBMS_SCHEDULER UTL_FILE WRR$_USER_MAP
DBMS_SQLPA V$DATABASE WRR$_WORKLOAD_ATTRIBUTES
DBMS_SQLTUNE V$INSTANCE WRR$_WORKLOAD_REPLAY_THREAD
DBMS_STANDARD V$PARAMETER XMLAGG
DBMS_SWRF_INTERNAL V$SYSTEM_PARAMETER XMLSEQUENCE
DBMS_SWRF_REPORT_INTERNAL WRI$_ALERT_HISTORY XMLTYPE
Documented No
Exceptions
Error Code Reason
ORA-20333 Bad string "<string>" to represent a boolean value
First Available 12.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtwrr.plb
Subprograms
ACQUIRE_WRR_LOCK (2) END_REPLAY_ACTIONS INVOKE_GET_FEATURE_BOOL_VALUE
ADD_CAPTURE EXPORT_STS INVOKE_KGHSFSNEWFILE
ADD_CAPTURE_BUCKET EXPORT_STS_FROM_CAPTURE INVOKE_READ_WMD
ADD_CAPTURE_STATS EXPORT_STS_FROM_REPLAY INVOKE_RELEASE_WRR_LOCK (2)
ADD_FILTER (2) EXPORT_UC_GRAPH INVOKE_UPDATE_WMD
ADD_REPLAY FILE_EXISTS IS_CAPTURE_USING_TBL_STORAGE
ADD_REPLAY_STATS FINALIZE_FIRST_PASS IS_CONSOLIDATED_DATABASE
ARG_MIN FINALIZE_STS_CAPTURE IS_CONTAINER_CDBROOT
BITOR FORMAT_INTERVAL IS_CONTAINER_PDB
BOOL_TO_STRING FORMAT_TIME_INTERVAL LIST_TO_TABLE
BUILD_CAPTURE_INFO_TAG GENERATE_CAPTURE_WID LIST_TO_TABLE_INT
CAPTURE_EXPORT_AWR GET_ASH_REPORT_REFERENCE LOAD_LOGON_ACTIONS
CAPTURE_UPDATE_EXP_STATUS GET_BOOLEAN_VALUE LOAD_WORKLOAD_ATTRIBUTES
CHECK_PLSQL_MODE_VALID GET_CAPINFO_INTERNAL OS_SECONDS
COMPARE_STS GET_CAPTURE_SIG PROCESS_SQLTEXT
COPY_FILTERS GET_DIR_PATH PROCESS_SQL_SCHEMA
COUNT_FILTERS GET_EXPORT_STATUS_I PUT_FILE
CREATE_DIR_OBJ GET_FILE REPLAY_EXPORT_AWR
CREATE_DIR_OBJ_TMP GET_PLSQL_MODE_INTERNAL REPLAY_REPORT_INTERNAL
CREATE_SNAPSHOT GET_SNAP_ASH_TIMES REPLAY_SUFFIX
DBG_TRACE GET_STS_NAME START_STS_CAPTURE
DB_DATE GET_UNIQUE_DBID STOP_SQL_SET_CAPTURE
DELETE_CAPTURE IMPORT_STS TO_DBTZ
DELETE_FILE IMPORT_STS_FROM_CAPTURE TRUNCATE_TABLE
DELETE_FILTER IMPORT_STS_FROM_REPLAY UPDATE_CAPTURE_STATS
DELETE_REPLAY IMPORT_UC_GRAPH UPDATE_PROPS
DIR_OBJ_EXISTS INIT_CAPTURE_STATS VALIDATE_ENCRYPTION_PASSWD
DROP_DIR_OBJ INVOKE_ACQUIRE_WRR_LOCK VALID_DEFAULT_ACTION
DROP_INDEX INVOKE_CHECK_SQLSET_PRIVS WI_TRANSFORM_ID_TO_STR
DROP_TABLE INVOKE_DIS_RES_SESS  
 
ADD_CAPTURE_BUCKET
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
Undocumented dbms_wrr_internal.arg_min(arr IN dbms_utility.index_table_type)
RETURN BINARY_INTEGER;
TBD
 
BITOR
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
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
Undocumented dbms_wrr_internal.check_plsql_mode_valid(
capture_id IN NUMBER,
pmode      IN BINARY_INTEGER,
errmsg     IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
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;
 
DIR_OBJ_EXISTS
Returns TRUE if the directory object exists and is accessible in the current schema dbms_wrr_internal.dir_obj_exists(dir_obj IN VARCHAR2)
RETURN BOOLEAN
SELECT directory_name
FROM dba_directories
ORDER BY 1;

BEGIN
  IF dbms_wrr_internal.dir_obj_exists('DATA_PUMP_DIR') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
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
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');
 
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;
/
 
FINALIZE_FIRST_PASS
Undocumented dbms_wrr_internal.finalize_first_pass(
capture_id IN NUMBER,
pmode      IN NUMBER);
TBD
 
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
 
FGET_ASH_REPORT_REFERENCE
Undocumented dbms_wrr_internal.get_ash_report_reference(
report_level IN VARCHAR2,
filter_list  IN VARCHAR2,
dbid         IN NUMBER,
start_time   IN DATE,
end_time     IN DATE)
RETURN VARCHAR2;
TBD
 
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;

DBMS_WRR_INTERNAL.GET_DIR_PATH('XMLDIR')
-----------------------------------------
/u01/orahome18/rdbms/xml
 
GET_PLSQL_MODE_INTERNAL
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
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
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
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
Undocumented but returns TRUE in a container database 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 a Consolidated Database

PL/SQL procedure successfully completed.
 
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
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
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;
SELECT dbms_wrr_internal.os_seconds
FROM dual;

OS_SECONDS
----------
1510445432
 
PROCESS_SQLTEXT
Undocumented dbms_wrr_internal.process_sqltext(capture_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;

SYSDATE              DBMS_WRR_INTERNAL.TO
-------------------- --------------------
25-JAN-2019 18:28:57 25-JAN-2019 00:28:57
 
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;

 COUNT(*)
---------
     2232

exec dbms_wrr_internal.truncate_table('TEST');

SELECT COUNT(*)
FROM test;

 COUNT(*)
---------
        0
 
UPDATE_PROPS
Undocumented dbms_wrr_internal.update_props(
prop_name  IN VARCHAR2,
prop_value IN VARCHAR2);
TBD
 
VALIDATE_ENCRYPTION_PASSWD
Undocumented dbms_wrr_internal.validate_encryption_passwd(capture_id IN NUMBER);
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
DBMS_WRR_PROTECTED
DBMS_WRR_REPORT
DBMS_WRR_STATE
DBMS_WRR_STATE_BASE
Directories
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx