Oracle DBMS_WRR_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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;
SELECT dbms_wrr_internal.bitor(20, 9)
FROM dual;

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

SELECT dbms_wrr_internal.bitor(20, -9)
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);
exec dbms_wrr_internal.create_dir_obj('WRR_DIR', 'C:\TEMP');

PL/SQL procedure successfully completed.

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;
BEGIN
  dbms_output.put_line(dbms_wrr_internal.create_snapshot(TRUE));
END;
/
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);
CREATE TABLE t (
testcol NUMBER);

CREATE INDEX ix_t
ON t(testcol);

exec dbms_wrr_internal.drop_index('IX_T');

PL/SQL procedure successfully completed.

SELECT index_name
FROM dba_indexes
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;
SELECT dbms_wrr_internal.format_interval(3.2547)
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;
SELECT dbms_wrr_internal.get_plsql_mode_internal('NATIVE')
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);
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 21c
What's New In 23c

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