Oracle DBMS_WORKLOAD_CAPTURE_I
Version 19c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Internal supporting package supporting DBMS_WORKLOAD_CAPTURE for capturing transactions for real application testing.
AUTHID DEFINER
Dependencies
DBA_HIST_PARAMETER DBMS_STANDARD PROPS$
DBA_HIST_SNAPSHOT DBMS_SWRF_INTERNAL SYS_IXMLAGG
DBA_SQLSET DBMS_SWRF_REPORT_INTERNAL V$DATABASE
DBA_WORKLOAD_CAPTURES DBMS_WORKLOAD_CAPTURE WRR$_CAPTURES
DBA_WORKLOAD_FILTERS DBMS_WORKLOAD_CAPTURE_LIB WRR$_CAPTURE_BUCKETS
DBMS_ADVISOR DBMS_WORKLOAD_REPLAY_I WRR$_CAPTURE_FILES
DBMS_ASSERT DBMS_WRR_INTERNAL WRR$_CAPTURE_SQLTEXT
DBMS_LOB DUAL XMLAGG
DBMS_REPORT PLITBLM XMLTYPE
DBMS_SCHEDULER    
Documented No
First Available 18c
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtwrr.plb
Subprograms
 
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);
-- do not know where filters are stored

exec dbms_workload_capture_i.delete_filter('UWFilter');
 
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
 n NUMBER;
BEGIN
  n := dbms_workload_capture_i.get_capture_info('CTEMP');
  dbms_output.put_line(n);
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
 
GET_STATE
Returns 1 if a session is being capture: Else 0 dbms_workload_capture_i.get_state RETURN BINARY_INTEGER;
SQL> SELECT dbms_workload_capture_i.get_state
  2  FROM dual;

 GET_STATE
----------
         0
 
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;

SELECT 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);
 
USER_CALLS_GRAPH
Undocumented dbms_workload_capture_i.user_calls_graph(capture_id IN NUMBER)
RETURN uc_graph_table PIPELINED;
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_AWRHUB
DBMS_AWRHUB_SERVER
DBMS_AWRHUB_SOURCE
DBMS_RAT_MASK
DBMS_REPLAYHUB
DBMS_WORKLOAD_CAPTURE
DBMS_WORKLOAD_REPLAY
DBMS_WORKLOAD_REPLAY_I
DBMS_WRR_INTERNAL
DBMS_WRR_STATE_BASE
What's New In 19c
What's New In 20c-21c

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