Oracle | DBMS_WORKLOAD_CAPTURE

Oracle DBMS_WORKLOAD_CAPTURE_I 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 supporting package supporting DBMS_WORKLOAD_CAPTURE for capturing transactions for real application testing.

AUTHID DEFINER

Dependencies

DBA_HIST_PARAMETER	DBMS_SCHEDULER	DBMS_WRR_PROTECTED
DBA_HIST_SNAPSHOT	DBMS_STANDARD	DUAL
DBA_SQLSET	DBMS_SWRF_REPORT_INTERNAL	PLITBLM
DBA_WORKLOAD_CAPTURES	DBMS_SYSTEM	SYS_IXMLAGG
DBA_WORKLOAD_FILTERS	DBMS_WORKLOAD_CAPTURE	V$DATABASE
DBMS_ADVISOR	DBMS_WORKLOAD_CAPTURE_LIB	WRR$_CAPTURES
DBMS_ASSERT	DBMS_WORKLOAD_REPLAY_I	WRR$_CAPTURE_BUCKETS
DBMS_LOB	DBMS_WORKLOAD_REPOSITORY	XMLTYPE
DBMS_REPORT	DBMS_WRR_INTERNAL

Documented No

First Available 18c

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtwrr.plb

Subprograms

ADD_FILTER	FINISH_CAPTURE_HELP	IMPORT_PERFORMANCE_DATA
DELETE_CAPTURE_INFO	GET_CAPTURE_INFO	IMPORT_UC_GRAPH
DELETE_FILTER	GET_CAPTURE_PATH	REPORT
EXPORT_AWR	GET_PERF_DATA_EXPORT_STATUS	START_BATCH_CAPTURE
EXPORT_PERFORMANCE_DATA	HANDLE_ENCRYPTION	START_CAPTURE
EXPORT_UC_GRAPH	IMPORT_AWR	SWITCH_BUCKET
FINISH_CAPTURE

ADD_FILTER

Adds a filter to capture only a subset of the workload

Overload 1

dbms_workload_capture_i.add_filter(

      fname      IN VARCHAR2 NOT NULL,

      fattribute IN VARCHAR2 NOT NULL,

      fvalue     IN VARCHAR2 NOT NULL);

Attribute	Data Type
Action	String
Instance	Number
Module	String
Program	String
Service	String
User	String

conn sys@pdbdev as sysdba

      

      exec dbms_workload_capture_i.add_filter('UWFilter', 'User', 'UWCLASS');

Overload 2 dbms_workload_capture_i.add_filter( fname IN VARCHAR2 NOT NULL, fattribute IN VARCHAR2 NOT NULL, fvalue IN NUMBER NOT NULL);

conn sys@pdbdev as sysdba

      

      exec dbms_workload_capture_i.add_filter('UWFilter', 'Instance', 2);

DELETE_CAPTURE_INFO

Deletes the rows in DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS that corresponds to the given workload capture id dbms_workload_capture_i.delete_capture_info(capture_id IN NUMBER);

SELECT id, name

      FROM dba_workload_captures;

      

      exec dbms_workload_capture_i.delete_capture_info(6);

      

      SELECT id, name

      FROM dba_workload_captures;

DELETE_FILTER

Deletes the filter with the given name dbms_workload_capture_i.delete_filter(fname IN VARCHAR2);

exec dbms_workload_capture_i.delete_filter('UWFilter');

      

      -- so far unable to find where filters are stored

EXPORT_AWR

Exports the AWR snapshots associated with a given capture_id dbms_workload_capture_i.export_awr(capture_id IN NUMBER);

exec dbms_workload_capture_i.export_awr(6);

EXPORT_PERFORMANCE_DATA

Exports the AWR snapshots associated with a given capture_id as well as the SQL set that may have been captured along with the workload dbms_workload_capture_i.export_performance_data(capture_id IN NUMBER);

exec dbms_workload_capture_i.export_performance_data(6);

EXPORT_UC_GRAPH

Undocumented dbms_workload_capture_i.export_uc_graph(capture_id IN NUMBER);

exec dbms_workload_capture_i.export_uc_graph(6);

FINISH_CAPTURE

Signals all connected sessions to stop the workload capture and then stops future requests to the database from being captured dbms_workload_capture_i.finish_capture( timeout IN NUMBER DEFAULT 30, reason IN VARCHAR2 DEFAULT NULL);

exec dbms_workload_capture_i.finish_capture(20, 'Demo Complete');

      

      SELECT name, error_message

      FROM dba_workload_captures;

FINISH_CAPTURE_HELP

Undocumented dbms_workload_capture_i.finish_capture_help( timeout IN NUMBER, reason IN VARCHAR2);

TBD

GET_CAPTURE_INFO

Looks into the workload capture present in the given directory and retrieves all the information regarding that capture, imports the information into the DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS views and returns the appropriate DBA_WORKLOAD_CAPTURES_ID dbms_workload_capture_i.get_capture_info(dir IN VARCHAR2) RETURN NUMBER;

set serveroutput on

      

      DECLARE

       retVal NUMBER;

      BEGIN

        retVal := dbms_workload_capture_i.get_capture_info('TMP');

        dbms_output.put_line(retVal);

      END;

      /

      

      SELECT name, start_scn, end_scn, duration_secs, filters_used, capture_size

      FROM dba_workload_captures;

GET_CAPTURE_PATH

Returns the full path to the capture files directory dbms_workload_capture_i.get_capture_path(capture_id IN NUMBER) RETURN VARCHAR2;

set serveroutput on

      

      DECLARE

       dirpath VARCHAR2(100);

      BEGIN

        dirpath := dbms_workload_capture_i.get_capture_path(6);

        dbms_output.put_line(dirpath);

      END;

      /

GET_PERF_DATA_EXPORT_STATUS

Populates awr_data and sts_data with the filenames of the exported performance data. If no data exists, NULL is set to the appropriate output variable dbms_workload_capture_i.get_perf_data_export_status( capture_id IN NUMBER, awr_data OUT VARCHAR2, sts_data OUT VARCHAR2);

TBD

HANDLE_ENCRYPTION

Based on the specified action will encrypt or decrypt the identified capture dbms_workload_capture_i.handle_encryption( action IN BINARY_INTEGER, src_dir IN VARCHAR2, dst_dir IN VARCHAR2, encryption IN VARCHAR2); -- options: 'AES128', 'AES192', 'AES256'

TBD

IMPORT_AWR

Imports an AWR snapshot that was exported earlier from the original capture system using DBMS_WORKLOAD_CAPTURE EXPORT_AWR. To avoid DBID conflicts, this function will generate a random DBID and use that DBID to populate the SYS AWR schema. The value used for DBID can be found in DBA_WORKLOAD_CAPTURES AWR_DBID. dbms_workload_capture_i.import_awr( cpature_id IN NUMBER staging_schema IN VARCHAR2 force_cleanup IN BOOLEAN) RETURN NUMBER;

TBD

IMPORT_PERFORMANCE_DATA

Imports the AWR snapshots from a given replay, provided those AWR snapshots were successfully exported earlier from the original replay system dbms_workload_capture_i.import_performance_data( capture_id IN NUMBER, staging_schema IN VARCHAR2, force_cleanup IN BOOLEAN) RETURN NUMBER;

TBD

IMPORT_UC_GRAPH

Undocumented dbms_workload_capture_i.import_uc_graph(capture_id IN NUMBER);

TBD

REPORT

Generates a report on the given workload capture dbms_workload_capture_i.report( capture_id IN NUMBER, format IN VARCHAR2);

SELECT id, name, status

      FROM dba_workload_captures;

      

      exec dbms_workload_capture_i.report(1, 'HTML');

START_BATCH_CAPTURE

Start workload capture and store data in different buckets. For instance, workload in 9AM - 10AM will be stored in bucket 1 while workload in 10AM - 12PM will be stored in bucket 2. dbms_workload_capture_i.start_batch_capture( name IN VARCHAR2, dir IN VARCHAR2, duration IN NUMBER, default_action IN VARCHAR2, auto_unrestrict IN BOOLEAN, capture_sts IN BOOLEAN, sts_cap_interval IN NUMBER, plsql_mode IN VARCHAR2, encryption IN VARCHAR2);

TBD

START_CAPTURE

Initiates a database wide workload capture dbms_workload_capture_i.start_capture( name IN VARCHAR2, dir IN VARCHAR2, duration IN NUMBER, default_action IN VARCHAR2, auto_unrestruct IN BOOLEAN, CAPTURE_STS IN BOOLEAN, STS_CAP_INTERVAL IN NUMBER, PLSQL_MODE IN VARCHAR2, ENCRYPTION IN VARCHAR2);

TBD

SWITCH_BUCKET

Signals all connected sessions to store workload captures into a new bucket. By default, SWITCH_BUCKET will create an AWR snapshot for the workload captured in the current bucket. dbms_workload_capture.switch_bucket(create_snapshot IN BOOLEAN DEFAULT TRUE);

exec dbms_workload_capture_i.switch_bucket(FALSE);