Oracle | DBMS_WRR

Oracle DBMS_WRR_INTERNAL Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

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 SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_WRR_INTERNAL' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_WRR_INTERNAL' ORDER BY 1;

Returns 80 objects

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_GLOBAL_LOCK	DROP_TABLE	INVOKE_GET_FEATURE_BOOL_VALUE
ACQUIRE_PDB_LOCK	END_REPLAY_ACTIONS	INVOKE_KGHSFSNEWFILE
ACQUIRE_WRC_LOCK	EXPORT_STS	INVOKE_READ_WMD
ADD_CAPTURE	EXPORT_STS_FROM_CAPTURE	INVOKE_UPDATE_WMD
ADD_CAPTURE_BUCKET	EXPORT_STS_FROM_REPLAY	IS_CAPTURE_USING_TBL_STORAGE
ADD_CAPTURE_STATS	EXPORT_UC_GRAPH	IS_CONSOLIDATED_DATABASE
ADD_FILTER	FILE_EXISTS	IS_CONTAINER_CDBROOT
ADD_REPLAY	FINALIZE_FIRST_PASS	IS_CONTAINER_PDB
ADD_REPLAY_STATS	FINALIZE_STS_CAPTURE	LIST_TO_TABLE
ARG_MIN	FORMAT_INTERVAL	LIST_TO_TABLE_INT
BITOR	FORMAT_TIME_INTERVAL	LOAD_LOGON_ACTIONS
BOOL_TO_STRING	GENERATE_CAPTURE_WID	LOAD_WORKLOAD_ATTRIBUTES
BUILD_CAPTURE_INFO_TAG	GET_ASH_REPORT_REFERENCE	OS_SECONDS
CAPTURE_EXPORT_AWR	GET_BOOLEAN_VALUE	PROCESS_SQLTEXT
CAPTURE_UPDATE_EXP_STATUS	GET_CAPINFO_INTERNAL	PROCESS_SQL_SCHEMA
CHECK_PLSQL_MODE_VALID	GET_CAPTURE_SIG	PUT_FILE
COMPARE_STS	GET_DIR_PATH	RELEASE_GLOBAL_LOCK
COPY_FILTERS	GET_EXPORT_STATUS_I	RELEASE_PDB_LOCK
COUNT_FILTERS	GET_FILE	RELEASE_WRC_LOCK
CREATE_DIR_OBJ	GET_PLSQL_MODE_INTERNAL	REPLAY_EXPORT_AWR
CREATE_DIR_OBJ_TMP	GET_RPT_DBID	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_CHECK_SQLSET_PRIVS	VALID_DEFAULT_ACTION
DROP_INDEX	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);

      

      PL/SQL procedure successfully completed.

ADD_FILTER

Undocumented

Overload 1 dbms_wrr_internal.add_filter( ftype IN VARCHAR2, fname IN VARCHAR2, fattribute IN VARCHAR2, fvalue IN VARCHAR2);

TBD

Overload 2 dbms_wrr_internal.add_filter( ftype IN VARCHAR2, fname IN VARCHAR2, fattribute IN VARCHAR2, fvalue IN NUMBER);

TBD

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);

      

      DBMS_WRR_INTERNAL.BITOR(20,9)

      -----------------------------

                                 29

      

      SELECT dbms_wrr_internal.bitor(20, -9);

      


      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;

      /

      Y

      N

      

      PL/SQL procedure successfully completed.

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', '/home/oracle');

      

      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;

exec dbms_output.put_line(dbms_wrr_internal.create_snapshot(TRUE));

      3646

      

      PL/SQL procedure successfully completed.

DBG_TRACE

Undocumented dbms_wrr_internal.dbg_trace( mesg IN VARCHAR2, print_stack IN BOOLEAN);

exec dbms_wrr_internal.dbg_trace('Test Message', TRUE);

      

      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;

      

      DB_DATE

      --------------------

      20-OCT-2024 15:45:57

      

      SELECT sysdate;

      

      SYSDATE

      --------------------

      20-OCT-2024 15:46:42

DELETE_FILE

Undocumented dbms_wrr_internal.delete_file( dir IN VARCHAR2, fname IN VARCHAR2);

exec dbms_wrr_internal.delete_file('CTEMP', 'test.txt');

      

      PL/SQL procedure successfully completed.

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

      

      PL/SQL procedure successfully completed.

DROP_DIR_OBJ

Drops a directory object

The proc has no error handling so if the directory does not exist or is not dropped it still claims to be have completed successfully dbms_wrr_internal.drop_dir_obj(dobj IN VARCHAR2);

CREATE OR REPLACE DIRECTORY wrrtemp AS '/home/oracle';

      

      col directory_name format a30

      col directory_path format a50

      

      SELECT *

      FROM dba_directories

      ORDER BY 2;

      

      exec dbms_wrr_internal.drop_dir_obj('WRRTEMP');

      

      PL/SQL procedure successfully completed.

      

      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

The proc has no error handling so if the table does not exist or is not dropped it still claims to be have completed successfully dbms_wrr_internal.drop_table(tbname IN VARCHAR2);

CREATE TABLE test AS

      SELECT * FROM tab$;

      

      exec dbms_wrr_internal.drop_table('SYS.ZZYZX');

      

      PL/SQL procedure successfully completed.

EXPORT_STS_FROM_CAPTURE

Undocumented dbms_wrr_internal.export_sts_from_capture(cid IN NUMBER);

TBD

EXPORT_STS_FROM_REPLAY

Undocumented dbms_wrr_internal.export_sts_from_replay( rid IN NUMBER, sts_dmp_file IN VARCHAR2);

TBD

FILE_EXISTS

Returns TRUE if a file exists, Otherwise FALSE

The file, in the demo at right, as shown does not exist and that is reported dbms_wrr_internal.file_exists( dir IN VARCHAR2, file IN VARCHAR2) RETURN BOOLEAN;

CREATE OR REPLACE DIRECTORY wrrtemp AS '/home/oracle';

      

      BEGIN

        IF dbms_wrr_internal.file_exists('WRRTEMP', 'FUBAR.TXT') THEN

          dbms_output.put_line('File Found');

        ELSE

          dbms_output.put_line('File Not Found');

        END IF;

      END;

      /

      File Not Found

      

      PL/SQL procedure successfully completed.

FINALIZE_FIRST_PASS

Undocumented dbms_wrr_internal.finalize_first_pass( capture_id IN NUMBER, pmode IN NUMBER);

TBD

FORMAT_INTERVAL (new 23ai parameters)

Formats an interval into a string in days, hours, minutes, and seconds dbms_wrr_internal.format_interval( unit_val IN NUMBER, unit_name IN VARCHAR2, output_days IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

SELECT dbms_wrr_internal.format_time_interval(1.1, 'DAY');

      

      DBMS_WRR_INTERNAL.FORMAT_INTERVAL(1.1, 'DAY')

      -----------------------------------------------

      1 day 2 hours 24 minutes 0 seconds

      

      SELECT dbms_wrr_internal.format_time_interval(1.1, 'DAY', 'X');

      

      DBMS_WRR_INTERNAL.FORMAT_INTERVAL(1.1, 'DAY', 
      'X')

      -----------------------------------------------

      26 hours 24 minutes 0 seconds

FORMAT_TIME_INTERVAL

Converts an integer and unit into its INTERVAL DAY TO SECOND representation dbms_wrr_internal.format_time_interval( unit_val IN NUMBER, unit_name IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_wrr_internal.format_time_interval(1.1, 'DAY');

      

      DBMS_WRR_INTERNAL.FORMAT_TIME_INTERVAL(45,'DAY')

      ------------------------------------------------

      26:24:00

GET_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;

      /

      FALSE

      

      PL/SQL procedure successfully completed.

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');

      

      DBMS_WRR_INTERNAL.GET_DIR_PATH('XMLDIR')

      ---------------------------------------------------

      /u01/app/oracle/product/23.0.0.0/dbhome_1/rdbms/xml

GET_FILE

Undocumented dbms_wrr_internal.get_file( dirname IN VARCHAR2, filename IN VARCHAR2, dir_id IN BINARY_INTEGER, rep_id IN BINARY_INTEGER, buffer IN OUT CLOB) RETURN BOOLEAN;

TBD

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');

      

      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

      -----------

       3091983078

      

      -- 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, 3091983078, stime, etime);

        dbms_output.put_line(TO_CHAR(stime));

        dbms_output.put_line(TO_CHAR(etime));

      END;

      /

      

      19-OCT-2024 01:17:11

      20-OCT-2024 15:29:46

      

      PL/SQL procedure successfully completed.

GET_UNIQUE_DBID

Random DBID number generator dbms_wrr_internal.get_unique_dbid RETURN NUMBER;

SELECT dbms_wrr_internal.get_unique_dbid;

      

      GET_UNIQUE_DBID

      ---------------

           1976369302

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;

      

      PL/SQL procedure successfully completed.

INVOKE_DIS_RES_SESS

Undocumented dbms_wrr_internal.invoke_dis_res_sess;

exec dbms_wrr_internal.invoke_dis_res_sess;

      

      PL/SQL procedure successfully completed.

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;

      /

      Not Invoked

      

      PL/SQL procedure successfully completed.

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 CDB$ROOT

      

      PL/SQL procedure successfully completed.

IS_CONTAINER_PDB

Returns FALSE when connected to CDB$ROOT as the root container is not a PDB dbms_wrr_internal.is_container_pdb RETURN BOOLEAN;

BEGIN

        IF dbms_wrr_internal.is_container_pdb THEN

          dbms_output.put_line('Is a PDB');

        ELSE

          dbms_output.put_line('Is not a PDB');

        END IF;

      END;

      /

      Is not a container DB

      

      PL/SQL procedure successfully completed.

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;

      

      OS_SECONDS

      ----------

      1510445432

PROCESS_SQLTEXT

Undocumented dbms_wrr_internal.process_sqltext(capture_id IN NUMBER);

TBD

PUT_FILE

Undocumented dbms_wrr_internal.put_file( dirname IN VARCHAR2, filename IN VARCHAR2, dir_id IN BINARY_INTEGER, rep_id IN BINARY_INTEGER, buffer IN OUT CLOB) RETURN BOOLEAN;

TBD

TO_DBTZ

Undocumented dbms_wrr_internal.to_dbtz(sys_dt IN DATE) RETURN DATE;

SELECT sysdate, dbms_wrr_internal.to_dbtz(sysdate-1);

      

      SYSDATE              DBMS_WRR_INTERNAL.TO

      -------------------- --------------------

      20-OCT-2024 16:10:01 19-OCT-2024 16:10:01

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

VALID_DEFAULT_ACTION

Undocumented dbms_wrr_internal.valid_default_action(default_action IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF dbms_wrr_internal.valid_default_action('ZZYZX') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      F

      

      PL/SQL procedure successfully completed.