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 is now availble. If you haven't you downloaded already: Why?

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.