Oracle | DBMS_WRR

Oracle DBMS_WRR_STATE 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 Constants that can be used to determine whether Workload Capture and Replay are currently active.

This package was present in 21.1 but was dropped as of 21.3 and found again in 23ai.

AUTHID N/A

Constants

Name	Data Type	Value
Capture_On	BOOLEAN	(dbms_workload_capture.get_state()=1);
Replay_On	BOOLEAN	(dbms_workload_replay.get_state()=1);

Dependencies

DBMS_RANDOM

VALUE

Documented No

First Available 12.2

Pragma PRAGMA restrict_references(dbms_wrr_state, WNDS);

Security Model Owned by SYS with EXECUTE granted to the DBA and EXECUTE_CATALOG_ROLE roles.

Source {ORACLE_HOME}/rdbms/admin/dbmswrr_state.sql

Demo

WRR State Demo The following demo code is from $ORACLE_HOME/rdbms/admin/dbmsrand.sql and shows how these constants are used. The function "VALUE" below is incomplete and will not compile outside of the full PL/SQL package.

FUNCTION value RETURN NUMBER PARALLEL_ENABLE IS

       randval NUMBER;

      BEGIN

        IF sys.dbms_wrr_state.Replay_On THEN

          randval := replay_random_number();

         IF randval IS NOT NULL THEN

            RETURN randval;

          END IF;

        END IF;

      

        counter := counter + 1;

        IF counter >= 55 THEN

          -- initialize if needed

          IF (need_init = TRUE) THEN

            seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') || USER || USERENV('SESSIONID'));

          ELSE

          -- need to generate 55 more results

            FOR i IN 0..30 LOOP

              randval := mem(i+24) + mem(i);

              IF (randval >= 1.0) THEN

                randval := randval - 1.0;

              END IF;

              mem(i) := randval;

            END LOOP;

      

            FOR i IN 31..54 LOOP

              randval := mem(i-31) + mem(i);

              IF (randval >= 1.0) THEN

                randval := randval - 1.0;

              END IF;

              mem(i) := randval;

            END LOOP;

          END IF;

          counter := 0;

        END IF;

      

        IF sys.dbms_wrr_state.Capture_On THEN

          record_random_number(mem(counter));

        END IF;

      

        RETURN mem(counter);

      END value;

      /