Oracle DBMS_WORKLOAD_REPOSITORY
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose API for managing the Workload Repository and performing operations such as managing snapshots and baselines for ASH and AWR reports.
AUTHID DEFINER
Background Process MMON - Automatic data purging every 7 days by default
Constants
Name Data Type Value
MAX_INTERVAL NUMBER 52560000 (100 years)
MIN_INTERVAL NUMBER 10 (10 minutes)
MAX_RETENTION NUMBER 52560000 (100 years)
MIN_RETENTION NUMBER 1440 (1 day)
Data Types CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_HTML_TYPE
AS OBJECT (output VARCHAR2(8000 CHAR));

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_HTML_TYPE_TABLE
AS TABLE OF AWRRPT_HTML_TYPE;

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_TEXT_TYPE
AS OBJECT (output VARCHAR2(80 CHAR));

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_TEXT_TYPE_TABLE
AS TABLE OF AWRRPT_TEXT_TYPE;

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_ROW_TYPE
AS OBJECT (
num_dfn AWRRPT_NUM_ARY,
vch_dfn AWRRPT_VCH_ARY,
clb_dfn AWRRPT_CLB_ARY);

CREATE OR REPLACE NONEDITIONABLE TYPE SYS.AWRRPT_INSTANCE_LIST_TYPE
AS TABLE OF NUMBER;
Dependencies
AWRBL_DETAILS_TYPE AWR_PDB_BASELINE DBMS_SWRF_INTERNAL
AWRBL_DETAILS_TYPE_TABLE AWR_PDB_BASELINE_DETAILS DBMS_SWRF_LIB
AWRBL_METRIC_TYPE AWR_PDB_SNAPSHOT DBMS_SWRF_REPORT_INTERNAL
AWRBL_METRIC_TYPE_TABLE AWR_PDB_SYSMETRIC_SUMMARY DBMS_UMF
AWRDRPT_TEXT_TYPE AWR_ROOT_BASELINE DBMS_UMF_PROTECTED
AWRDRPT_TEXT_TYPE_TABLE AWR_ROOT_BASELINE_DETAILS DBMS_WORKLOAD_CAPTURE_I
AWRRPT_HTML_TYPE DBA_HIST_BASELINE DBMS_WORKLOAD_REPLAY_I
AWRRPT_HTML_TYPE_TABLE DBA_HIST_BASELINE_DETAILS DBMS_WRR_INTERNAL
AWRRPT_INSTANCE_LIST_TYPE DBMS_ASH_INTERNAL DBMS_XPLAN
AWRRPT_TEXT_TYPE DBMS_ASSERT MGMT_BSLN_INTERVALS
AWRRPT_TEXT_TYPE_TABLE DBMS_AWR_WAREHOUSE_SERVER PLITBLM
AWRSQRPT_TEXT_TYPE DBMS_AWR_WAREHOUSE_SOURCE PRVT_AWR_VIEWER
AWRSQRPT_TEXT_TYPE_TABLE DBMS_MANAGEMENT_PACKS V$INSTANCE
AWR_CDB_BASELINE DBMS_REPORT WRM$_WR_CONTROL
AWR_CDB_BASELINE_DETAILS DBMS_STANDARD XMLTYPE
Documented Yes
First Available 10.1
Initialization Parameters
_awr_cdbperf_threshold _awr_mmon_cpuusage _awr_restrict_mode
_awr_corrupt_mode _awr_mmon_deep_purge_all_expired _awr_sql_child_limit
_awr_disabled_flush_tables _awr_mmon_deep_purge_interval _flush_plan_in_awr_sql
_awr_flush_threshold_metrics _awr_pdb_registration_enabled _remote_awr_enabled
_awr_flush_workload_metrics _awr_remote_target_dblink  
Security Model Owned by SYS with EXECUTE granted to DBA, OEM_MONITOR, and SYSUMF_ROLE roles
Source {ORACLE_HOME}/rdbms/admin/dbmsawr.sql

-- also the following scripts create the AWR schema objects
{ORACLE_HOME}/rdbms/admin/catawr.sql
{ORACLE_HOME}/rdbms/admin/catawrpd.sql
{ORACLE_HOME}/rdbms/admin/catawrtb.sql
{ORACLE_HOME}/rdbms/admin/catawrwv.sql
-- all must be run as SYSDBA
Subprograms
ADD_COLORED_SQL AWR_REPORT_HTML LOAD
ASH_GLOBAL_REPORT_HTML AWR_SET_REPORT_THRESHOLDS LOCAL_AWR_DBID
ASH_GLOBAL_REPORT_TEXT AWR_SQL_REPORT_HTML MODIFY_BASELINE_WINDOW_SIZE
ASH_REPORT_ANALYTICS AWR_SQL_REPORT_TEXT MODIFY_SNAPSHOT_SETTINGS
ASH_REPORT_HTML CONTROL_RESTRICTED_SNAPSHOT MODIFY_TABLE_SETTINGS
ASH_REPORT_TEXT CREATE_BASELINE PURGE_SQL_DETAILS
AWR_DIFF_REPORT_HTML CREATE_BASELINE_TEMPLATE REGISTER_REMOTE_DATABASE
AWR_DIFF_REPORT_TEXT CREATE_REMOTE_SNAPSHOT REMOVE_COLORED_SQL
AWR_EXP CREATE_SNAPSHOT RENAME_BASELINE
AWR_GLOBAL_DIFF_REPORT_HTML DROP_BASELINE SELECT_BASELINE_DETAILS
AWR_GLOBAL_DIFF_REPORT_TEXT DROP_BASELINE_TEMPLATE SELECT_BASELINE_METRIC
AWR_GLOBAL_REPORT_HTML DROP_SNAPSHOT_RANGE UNREGISTER_REMOTE_DATABASE
AWR_GLOBAL_REPORT_TEXT EXTRACT UPDATE_DATAFILE_INFO
AWR_IMP GET_AWR_VIEW_NAME_BY_LOCATION UPDATE_OBJECT_INFO
AWR_REPORT    
 
ADD_COLORED_SQL
Routine to add a colored SQL ID. If an SQL ID is colored, it will always be captured in every snapshot, independent of its level of activities (i.e. does not have to be a TOP SQL). Capturing will occur if the SQL is found in the cursor cache at snapshot time. dbms_workload_repository.add_colored_sql(
sql_id IN VARCHAR2,
dbid   IN NUMBER DEFAULT NULL);
desc wrm$_colored_sql

SELECT * FROM wrm$_colored_sql;

SELECT dbid
FROM v$database;

SELECT sql_id
FROM gv$sql
WHERE rownum < 101;

exec dbms_workload_repository.add_colored_sql('5rygsj4dbw6jt', 428676178);

SELECT * FROM wrm$_colored_sql;

exec dbms_workload_repository.remove_colored_sql('5rygsj4dbw6jt', 428676178);

SELECT * FROM wrm$_colored_sql;
 
ASH_GLOBAL_REPORT_HTML
Return the ASH Spot report in text format as one column of VARCHAR2(80) dbms_workload_repository.ash_global_report_html(
l_dbid         IN NUMBER,
l_inst_num     IN VARCHAR2,
l_btime        IN DATE,
l_etime        IN DATE,
l_options      IN NUMBER   DEFAULT 0,
l_slot_width   IN NUMBER   DEFAULT 0,
l_sid          IN NUMBER   DEFAULT NULL,
l_sql_id       IN VARCHAR2 DEFAULT NULL,
l_wait_class   IN VARCHAR2 DEFAULT NULL,
l_service_hash IN NUMBER   DEFAULT NULL,
l_module       IN VARCHAR2 DEFAULT NULL,
l_action       IN VARCHAR2 DEFAULT NULL,
l_client_id    IN VARCHAR2 DEFAULT NULL,
l_plsql_entry  IN VARCHAR2 DEFAULT NULL,
l_data_src     IN NUMBER   DEFAULT 0,
l_container    IN VARCHAR2 DEFAULT NULL)
RETURN awrrpt_html_type_table PIPELINED;
conn sys@pdbdev as sysdba

SELECT dbid
FROM v$database;

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_html(428676178, 1, SYSDATE-1, SYSDATE, l_wait_class=>'Other'));
 
ASH_GLOBAL_REPORT_TEXT
Returns the ASH Spot report in html format as one column of VARCHAR2(500) dbms_workload_repository.ash_global_report_text(
l_dbid         IN NUMBER,
l_inst_num     IN VARCHAR2,
l_btime        IN DATE,
l_etime        IN DATE,
l_options      IN NUMBER   DEFAULT 0,
l_slot_width   IN NUMBER   DEFAULT 0,
l_sid          IN NUMBER   DEFAULT NULL,
l_sql_id       IN VARCHAR2 DEFAULT NULL,
l_wait_class   IN VARCHAR2 DEFAULT NULL,
l_service_hash IN NUMBER   DEFAULT NULL,
l_module       IN VARCHAR2 DEFAULT NULL,
l_action       IN VARCHAR2 DEFAULT NULL,
l_client_id    IN VARCHAR2 DEFAULT NULL,
l_plsql_entry  IN VARCHAR2 DEFAULT NULL,
l_data_src     IN NUMBER   DEFAULT 0,
l_container    IN VARCHAR2 DEFAULT NULL)
RETURN awrdrpt_text_type_table PIPELINED;
conn sys@pdbdev as sysdba

SELECT dbid
FROM v$database;

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(428676178, 1, SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));
 
ASH_REPORT_ANALYTICS
Return the ASH (analytics) active report in HTML format dbms_workload_repository.ash_report_analytics(
dbid         IN NUMBER   := NULL,
inst_id      IN NUMBER   := NULL,
begin_time   IN DATE,
end_time     IN DATE,
report_level IN VARCHAR2 := NULL,
filter_list  IN VARCHAR2 := NULL)
RETURN CLOB;
SELECT dbid
FROM v$database;

SELECT dbms_workload_repository.ash_report_analytics(1863203691, 1, SYSDATE-1/24, SYSDATE)
FROM dual;

DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_ANALYTICS(428676178,1,SYSDATE-1/24,SYSDATE)
--------------------------------------------------------------------------------
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
  <script language="javascript" type="text/javascript">
   <!--
      var version = "19.0.0.0.0";
      var swf_base_path = "https://download.oracle.com/otn_software/omx/";
          document.write('<script language="javascript" type="text/javascript" '
+
'src="' + swf_base_path + 'emsaasui/emcdbms-dbcsperf/act
ive-report/scripts/activeReportInit.js?' +
Math.floor((new Date()).getTime()/(7*24*60*60*1000)) +
'"></' + 'script>');
-->
</script>
</head>
<body onload="sendXML();">
<script type="text/javascript">
writeIframe();
  </script>
  <script id="fxtmodel" type="text/xml">
   <!--FXTMODEL-->
   <report db_version="19.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores
="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-18000" pac
ks="2" encode="base64" compress="zlib">
<report_id><![CDATA[/orarep/ash/viewer%3fbegin_time%3d01%3a57%3a31%2008/07/2
019%26dbid%3d1863203691%26end_time%3d02%3a57%3a31%2008/07/2019%26inst_id%3d1%26p
arent_report%3ddefault]]></report_id>
           eAFtj7sSgyAQRXu/guEHAI2vDNKlSJWMk55RIQmFwAAW+fuAYx6F3e6eu3vvUiet

cYHbwQ2zDNJ5lgFAxagEI01V5LioWkLROkhEaR94ghR9yjQe5UNpHtQsGSbHsj4W
BOAG4RrlmLQU/fEkl1ps4nxH/KUZRTv56GQXrvTdrFGfxgegY/gOXk99f75dSgj0
MvPJOOk7eNg6u8Qmh2lne2PQkwRKdJBAtJ5C6RaLpj8DOhrxivQNZrJZug==
            </report>
   <!--FXTMODEL-->
  </script>
 </body>
</html>
 
ASH_REPORT_HTML
Display the ASH report in HTML dbms_workload_repository.ash_report_html(
l_dbid         IN NUMBER,
l_inst_num     IN NUMBER,
l_btime        IN DATE,
l_etime        IN DATE,
l_options      IN NUMBER   DEFAULT 0,
l_slot_width   IN NUMBER   DEFAULT 0,
l_sid          IN NUMBER   DEFAULT NULL,
l_sql_id       IN VARCHAR2 DEFAULT NULL,
l_wait_class   IN VARCHAR2 DEFAULT NULL,
l_service_hash IN NUMBER   DEFAULT NULL,
l_module       IN VARCHAR2 DEFAULT NULL,
l_action       IN VARCHAR2 DEFAULT NULL,
l_client_id    IN VARCHAR2 DEFAULT NULL,
l_plsql_entry  IN VARCHAR2 DEFAULT NULL)
RETURN awrrpt_html_type_table PIPELINED;
SELECT dbid
FROM v$database;

SELECT inst_id
FROM gv$instance;

SELECT sample_time
FROM gv$active_session_history
ORDER BY 1;

set pagesize 0
set linesize 121

spool c:\temp\ash_rpt.html

SELECT * FROM TABLE(dbms_workload_repository.ash_report_html(428676178, 1, SYSDATE-30/1440, SYSDATE-1/1440));

spool off
Alternative ASH HTML Report define report_type = 'html'
define begin_time = '-30'
define duration = ''
define report_name = 'c:\temp\ashrpt.html'
@?/rdbms/admin/ashrpt
Alternative ASH HTML Report define report_type = 'html'
define begin_time = '-30'
define duration = ''
define report_name = 'c:\temp\ashrpt.html'
@?/rdbms/admin/ashrpti
 
ASH_REPORT_TEXT
Display the ASH report in TEXT dbms_workload_repository.ash_report_text(
l_dbid         IN NUMBER,
l_inst_num     IN NUMBER,
l_btime        IN DATE,
l_etime        IN DATE,
l_options      IN NUMBER   DEFAULT 0,
l_slot_width   IN NUMBER   DEFAULT 0,
l_sid          IN NUMBER   DEFAULT NULL,
l_sql_id       IN VARCHAR2 DEFAULT NULL,
l_wait_class   IN VARCHAR2 DEFAULT NULL,
l_service_hash IN NUMBER   DEFAULT NULL,
l_module       IN VARCHAR2 DEFAULT NULL,
l_action       IN VARCHAR2 DEFAULT NULL,
l_client_id    IN VARCHAR2 DEFAULT NULL,
l_plsql_entry  IN VARCHAR2 DEFAULT NULL)
RETURN awrrpt_text_type_table PIPELINED;
SELECT dbid
FROM v$database;

SELECT inst_id
FROM gv$instance;

SELECT sample_time
FROM gv$active_session_history
ORDER BY 1;

set pagesize 0
set linesize 121

spool c:\temp\ash_rpt.html

SELECT * FROM TABLE(dbms_workload_repository.ash_report_text(428676178, 1, SYSDATE-30/1440, SYSDATE-1/1440));

spool off
Alternative ASH Text Report define report_type = 'text'
define begin_time = '-30'
define duration = ''
define report_name = 'c:\temp\ashrpt.txt'
@?/rdbms/admin/ashrpt
Alternative ASH Text Report define report_type = 'text'
define begin_time = '-30'
define duration = ''
define report_name = 'c:\temp\ashrpt.txt'
@?/rdbms/admin/ashrpti
 
AWR_DIFF_REPORT_HTML
This table function displays the AWR Compare Periods Report in HTML format as  one column of VARCHAR2(5000) dbms_workload_repository.awr_diff_report_html(
dbid1     IN NUMBER,
inst_num1 IN NUMBER,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN NUMBER,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED;
spool /stage/diffrep_node5.html

SELECT * FROM TABLE(
dbms_workload_repository.awr_diff_report_html(782247420, 5, 7492, 7503, 782247420, 5, 7664, 7675));

spool off
 
AWR_DIFF_REPORT_TEXT
This table function displays the AWR Compare Periods Report in TEXT format. The pipelined output is one column of VARCHAR2(240). dbms_workload_repository.awr_diff_report_text(
dbid1     IN NUMBER,
inst_num1 IN NUMBER,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN NUMBER,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED;
spool /stage/diffrep_node6.html

SELECT * FROM TABLE(
dbms_workload_repository.awr_diff_report_text(782247420, 6, 7492, 7503, 782247420, 6, 7664, 7675));

spool off
 
AWR_EXP (new 21c)
Exports AWR data from the SYS schema into a dump file dbms_workload_repository.awr_exp(
dmpfile IN VARCHAR2,
dmpdir  IN VARCHAR2,
dbid    IN NUMBER,
bid     IN NUMBER,
eid     IN NUMBER);
TBD
 
AWR_GLOBAL_DIFF_REPORT_HTML
Displays the Gobal AWR Compare Periods Report in HTML format. The pipelined output is one column of VARCHAR2(1500).

Overload 1		 dbms_workload_repository.awr_global_diff_report_html(
dbid1     IN NUMBER,
inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED;
TBD
Overload 2

Encapsulated in awrgdrpt.sql, awrgdrpi.sql, and awrgdinp.sql		 dbms_workload_repository.awr_global_diff_report_html(
dbid1     IN NUMBER,
inst_num1 IN VARCHAR2,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN VARCHAR2,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED;
SELECT dbid FROM v$database;

set feedback off
set heading on
set linesize 1500
set termout on
set trim on
set trimspool on
set veri off
col instart_fmt noprint
col inst_name format a12 heading 'Instance'
col db_name format a12 heading 'DB Name'
col snap_id format 99999990 heading 'Snap Id'
col snapdat format a18 heading 'Snap Started' just c
col lvl format 99 heading 'Snap|Level'
break on inst_name on db_name on host on instart_fmt skip 1
ttitle off

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
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;

set trimspool on
set trim on
spool c:\temp\demo.html

SELECT * FROM TABLE(dbms_workload_repository.awr_global_diff_report_html(2497516142, '1', 65, 70, 2497516142, '1', 71, 75));

spool off
 
AWR_GLOBAL_DIFF_REPORT_TEXT
Displays the Global AWR Compare Periods Report in text format. The output is one column of VARCHAR2(320)

Overload 1		 dbms_workload_repository.awr_global_diff_report_text(
dbid1     IN NUMBER,
inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED;
TBD
Overload 2 dbms_workload_repository.awr_global_diff_report_text(
dbid1     IN NUMBER,
inst_num1 IN VARCHAR2,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN VARCHAR2,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED;
SELECT dbid FROM v$database;

col instart_fmt noprint
col inst_name format a12 heading 'Instance'
col db_name format a12 heading 'DB Name'
col snap_id format 99999990 heading 'Snap Id'
col snapdat format a18 heading 'Snap Started' just c
col lvl format 99 heading 'Snap|Level'
set heading on
break on inst_name on db_name on host on instart_fmt skip 1
ttitle off

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
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;

set trimspool on
set trim on
spool c:\temp\demo.txt

SELECT * FROM TABLE(dbms_workload_repository.awr_global_diff_report_text(2497516142, '1', 65, 70, 2497516142, '1', 71, 75));

spool off
 
AWR_GLOBAL_REPORT_HTML
Displays the AWR report in HTML

Overload 1		 dbms_workload_repository.awr_global_report_html(
l_dbid     IN NUMBER,
l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;
TBD
Overload 2 dbms_workload_repository.awr_global_report_html(
l_dbid     IN NUMBER,
l_inst_num IN VARCHAR2,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;
SELECT dbid FROM v$database;

col instart_fmt noprint
col inst_name format a12 heading 'Instance'
col db_name format a12 heading 'DB Name'
col snap_id format 99999990 heading 'Snap Id'
col snapdat format a18 heading 'Snap Started' just c
col lvl format 99 heading 'Snap|Level'
set heading on
break on inst_name on db_name on host on instart_fmt skip 1
ttitle off

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
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;

set trimspool on
set trim on
spool c:\temp\demo.html

SELECT * FROM TABLE(dbms_workload_repository.awr_global_report_html(428676178, '1', 10548, 10550, 0));

spool off
 
AWR_GLOBAL_REPORT_TEXT
Displays the AWR report in Text

Overload 1		 dbms_workload_repository.awr_global_report_text(
l_dbid     IN NUMBER,
l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;
TBD
Overload 2 dbms_workload_repository.awr_global_report_text(
l_dbid     IN NUMBER,
l_inst_num IN VARCHAR2,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;
SELECT dbid FROM v$database;

col instart_fmt noprint
col inst_name format a12 heading 'Instance'
col db_name format a12 heading 'DB Name'
col snap_id format 99999990 heading 'Snap Id'
col snapdat format a18 heading 'Snap Started' just c
col lvl format 99 heading 'Snap|Level'
set heading on
break on inst_name on db_name on host on instart_fmt skip 1
ttitle off

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
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;

set trimspool on
set trim on
spool c:\temp\demo.txt

SELECT * FROM TABLE(dbms_workload_repository.awr_global_report_text(2497516142, '1', 65, 70, 0));

spool off
 
AWR_IMP
Loads AWR data from a dump file into the SYS schema dbms_workload_repository.awr_imp(
dmpfile  IN VARCHAR2 DEFAULT 'awrdat',
dmpdir   IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
new_dbid IN NUMBER   DEFAULT NULL);
exec dbms_workload_repository.awr_imp;
 
AWR_REPORT
Returns an AWR report in the form of a CLOB dbms_workload_repository.awr_report(
l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER   DEFAULT 0,
l_format   IN VARCHAR2 DEFAULT 'html')
RETURN CLOB;
CREATE TABLE awr_report_repository(
awr_date DATE,
awr_clob CLOB);

INSERT INTO awr_report_repository
(awr_date, awr_clob)
VALUES
(SYSDATE, dbms_workload_repository.awr_report(1262297360, 1, 19500112, 19500136);
COMMIT;
 
AWR_REPORT_HTML
Display the AWR report in HTML dbms_workload_repository.awr_report_html(
l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
See AWR Report demo linked at the bottom of the page
 
AWR_REPORT_TEXT
Display the AWR report in ASCII text dbms_workload_repository.awr_report_text(
l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
See AWR Report demo linked at the bottom of the page
 
AWR_SET_REPORT_THRESHOLDS
Allows configuring of specified report thresholds. Allows control of the number of report rows dbms_workload_repository.awr_set_report_thresholds(
top_n_events       IN NUMBER DEFAULT NULL,
top_n_files        IN NUMBER DEFAULT NULL,
top_n_segments     IN NUMBER DEFAULT NULL,
top_n_services     IN NUMBER DEFAULT NULL,
top_n_sql          IN NUMBER DEFAULT NULL,
top_n_sql_max      IN NUMBER DEFAULT NULL,
top_sql_pct        IN NUMBER DEFAULT NULL,
shmem_threshold    IN NUMBER DEFAULT NULL,
versions_threshold IN NUMBER DEFAULT NULL,
op_n_disks         IN NUMBER DEFAULT NULL,
outlier_pct        IN NUMBER DEFAULT NULL,
outlier_cpu_pct    IN NUMBER DEFAULT NULL);
exec dbms_workload_repository.awr_set_report_thresholds(9, 8, 7, 6, 5, 4, 3, 2, 1);

-- used by $ORACLE_HOME/rdbms/admin/awrgdrpi.sql and awrgrpti.sql
 
AWR_SQL_REPORT_HTML
Display the AWR SQL report in HTML format dbms_workload_repository.awr_sql_report_html(
l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_sqlid    IN VARCHAR2,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;
SELECT dbid FROM v$database;

SELECT inst_id
FROM gv$instance;

set pagesize 0
set linesize 121
col instart_fmt noprint
col inst_name format a12 heading 'Instance'
col db_name format a12 heading 'DB Name'
col snap_id format 99999990 heading 'Snap Id'
col snapdat format a18 heading 'Snap Started' just c
col lvl format 99 heading 'Snap|Level'
set heading on
break on inst_name on db_name on host on instart_fmt skip 1
ttitle off

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
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;

SELECT sql_id
FROM gv$active_session_history
WHERE TRUNC(sql_exec_start) = TRUNC(SYSDATE);

spool c:\temp\awr_sql_rpt.html

SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_html(428676178, 1, 1230, 1231, 'a01hp0psv0rrh'));

spool off
 
AWR_SQL_REPORT_TEXT
Display the AWR SQL report in TEXT format dbms_workload_repository.awr_sql_report_text(
l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_sqlid    IN VARCHAR2,
l_options  IN NUMBER DEFAULT 0)
RETURN awrsqrpt_text_type_table PIPELINED;
SELECT dbid FROM v$database;

SELECT inst_id
FROM gv$instance;

set pagesize 0
set linesize 121
col instart_fmt noprint
col inst_name format a12 heading 'Instance'
col db_name format a12 heading 'DB Name'
col snap_id format 99999990 heading 'Snap Id'
col snapdat format a18 heading 'Snap Started' just c
col lvl format 99 heading 'Snap|Level'
set heading on
break on inst_name on db_name on host on instart_fmt skip 1
ttitle off

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
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;

SELECT sql_id
FROM gv$active_session_history
WHERE TRUNC(sql_exec_start) = TRUNC(SYSDATE);

spool c:\temp\awr_sql_rpt.txt

SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_text(428676178, 1, 1230, 1231, 'a01hp0psv0rrh'));

spool off
 
CONTROL_RESTRICTED_SNAPSHOT
Controls if AWR snapshots are allowed to occur even if the restricted session mode has been enabled for the database. TRUE allows snapshot capture in restricted session mode.

It does so by modifying an undocumented initialization parameter as shown.		 dbms_workload_repository.control_restricted_snapshot(allow IN BOOLEAN);
col name format a25
col description format a30

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
AND lower(x.ksppdesc) like '%awr%restrict%'
ORDER BY 1;

exec dbms_workload_repository.control_restricted_snapshot(TRUE);

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
AND lower(x.ksppdesc) like '%awr%restrict%'
ORDER BY 1;
 
CREATE_BASELINE
Creates a baseline returns the baseline_id

Overload 1		 dbms_workload_repository.create_baseline(
start_snap_id IN NUMBER,
end_snap_id   IN NUMBER,
baseline_name IN VARCHAR2,
dbid          IN NUMBER DEFAULT NULL
expiration    IN NUMBER DEFAULT NULL);
SELECT dbid FROM v$database;

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;

SELECT baseline_name, dbid
FROM dba_hist_baseline;

exec dbms_workload_repository.create_baseline(1199, 1207, 'UW_BASE', 428676178);

SELECT baseline_name, dbid
FROM dba_hist_baseline;
Overload 2 dbms_workload_repository.create_baseline(
start_snap_id IN NUMBER,
end_snap_id   IN NUMBER,
baseline_name IN VARCHAR2,
dbid          IN NUMBER DEFAULT NULL,
expiration    IN NUMBER DEFAULT NULL)
RETURN NUMBER;
SELECT dbid FROM v$database;

set linesize 121
col startup_time format a40

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;

SELECT baseline_name, dbid
FROM dba_hist_baseline;

set serveroutput on

DECLARE
 i dba_hist_baseline.baseline_id%TYPE;
BEGIN
  i := dbms_workload_repository.create_baseline(1199, 1207, 'UW_BASE', 428676178);
  dbms_output.put_line(TO_CHAR(i));
END;
/

SELECT baseline_id, baseline_name
FROM dba_hist_baseline;
Overload 3 dbms_workload_repository.create_baseline(
start_time    IN DATE,
end_time      IN DATE,
baseline_name IN VARCHAR2,
dbid          IN NUMBER DEFAULT NULL,
expiration    IN NUMBER DEFAULT NULL);
exec dbms_workload_repository.create_baseline(SYSDATE-2/24, SYSDATE-1/24, 'UW_BASE', 428676178);
Overload 4 dbms_workload_repository.create_baseline(
start_time    IN DATE,
end_time      IN DATE,
baseline_name IN VARCHAR2,
dbid          IN NUMBER DEFAULT NULL,
expiration    IN NUMBER DEFAULT NULL)
RETURN NUMBER;
DECLARE
 i dba_hist_baseline.baseline_id%TYPE;
BEGIN
  i := dbms_workload_repository.create_baseline(SYSDATE-2/24, SYSDATE-1/24, 'UW_BASE', 428676178);
  dbms_output.put_line(TO_CHAR(i));
END;
/
 
CREATE_BASELINE_TEMPLATE
Creates a Baseline Template for a single time period. There will be a MMON task that will use these inputs to create a Baseline for the time period when the time comes.

Overload 1		 dbms_workload_repository.create_baseline_template(
start_time    IN DATE,
end_time      IN DATE,
baseline_name IN VARCHAR2,
template_name IN VARCHAR2,
expiration    IN NUMBER DEFAULT NULL,
dbid          IN NUMBER DEFAULT NULL);
desc dba_hist_baseline_template

SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;

SELECT baseline_name, dbid
FROM dba_hist_baseline;

exec dbms_workload_repository.create_baseline_template(SYSDATE+1/1440, SYSDATE+5/1440, 'UW_BASE2', 'UW_TEMPLATE', 1);

SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;
Overload 2 dbms_workload_repository.create_baseline_template(
day_of_week          IN VARCHAR2,
hour_in_day          IN NUMBER,
duration             IN NUMBER,
start_time           IN DATE,
end_time             IN DATE,
baseline_name_prefix IN VARCHAR2,
template_name        IN VARCHAR2,
expiration           IN NUMBER DEFAULT 35,
dbid                 IN NUMBER DEFAULT NULL);
TBD
 
CREATE_REMOTE_SNAPSHOT
Create a manual AWR snapshot from a remote database

Overload 1		 dbms_workload_repository.create_remote_snapshot(
node_id     IN NUMBER,
flush_level IN VARCHAR2 DEFAULT 'BESTFIT');
TBD
Overload 2 dbms_workload_repository.create_remote_snapshot(
node_id     IN NUMBER,
flush_level IN VARCHAR2 DEFAULT 'BESTFIT')
RETURN NUMBER;
TBD
Overload 3 dbms_workload_repository.create_remote_snapshot(
node_name     IN VARCHAR2,
topology_name IN VARCHAR2 DEFAULT NULL,
flush_level   IN VARCHAR2 DEFAULT 'BESTFIT');
TBD
Overload 4 dbms_workload_repository.create_remote_snapshot(
node_name     IN VARCHAR2,
topology_name IN VARCHAR2 DEFAULT NULL,
flush_level   IN VARCHAR2 DEFAULT 'BESTFIT')
RETURN NUMBER;
TBD
 
CREATE_SNAPSHOT
Create snapshot and return snapshot ID

Overload 1		 dbms_workload_repository.create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN NUMBER;

Flush Levels
ALL
TYPICAL
set linesize 121
col begin_interval_time format a30
col end_interval_time format a30

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;

set serveroutput on

DECLARE
 i dba_hist_snapshot.snap_id%TYPE;
BEGIN
  i := dbms_workload_repository.create_snapshot;
  dbms_output.put_line(TO_CHAR(i));
END;
/

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;
Overload 2 dbms_workload_repository.create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL');

Flush Levels
ALL
TYPICAL
col begin_interval_time format a30
col end_interval_time format a30

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;

exec dbms_workload_repository.create_snapshot;

SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2;
 
DROP_BASELINE
Drop a baseline dbms_workload_repository.drop_baseline(
baseline_name IN VARCHAR2,
cascade       IN BOOLEAN DEFAULT FALSE,
dbid          IN NUMBER  DEFAULT NULL);

Cascade
FALSE Drop baseline but not snapshots
TRUE Drops baseline and snapshots
SELECT baseline_name, dbid
FROM dba_hist_baseline;

exec dbms_workload_repository.drop_baseline('UW_BASE', FALSE, 428676178);

SELECT baseline_name, dbid
FROM dba_hist_baseline;
 
DROP_BASELINE_TEMPLATE
Drops a Baseline Template dbms_workload_repository.drop_baseline_template(
template_name IN VARCHAR2,
dbid          IN NUMBER DEFAULT NULL);
SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;

exec dbms_workload_repository.drop_baseline_template('UW_TEMPLATE');

SELECT dbid, template_id, template_name, template_type
FROM dba_hist_baseline_template;
 
DROP_SNAPSHOT_RANGE
Drop a range of snapshots dbms_workload_repository.drop_snapshot_Range(
low_snap_id  IN NUMBER,
high_snap_id IN NUMBER
dbid         IN NUMBER DEFAULT NULL);
set linesize 121
col startup_time format a40

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;

exec dbms_workload_repository.drop_snapshot_range(1105, 1199);

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;
 
EXTRACT
Extracts AWR data from the AWR schema and writes it to a file dbms_workload_repository.extract(
schname         IN VARCHAR2,
dmpfile         IN VARCHAR2 DEFAULT 'awrdat',
dmpdir          IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
new_dbid        IN NUMBER   DEFAULT NULL,
dup_snapshot_ok IN BOOLEAN  DEFAULT TRUE,
gather_stats    IN BOOLEAN  DEFAULT FALSE,
source_name     IN VARCHAR2 DEFAULT NULL,
logfile         IN VARCHAR2 DEFAULT NULL,
logdir          IN VARCHAR2 DEFAULT NULL);
exec dbms_workload_repository.extract('XDB');

-- this may take a number of minutes to complete
-- monitor the dmpdir to monitor the progress
-- it can also generate a file of substantial size
-- as well as a valuable log file listing what was extracted
 
GET_AWR_VIEW_NAME_BY_LOCATION
Returns an appropriate AWR view name, given a DBA_HIST view name and AWR location dbms_workload_repository.get_awr_view_name_by_location(
dba_hist_view_name IN VARCHAR2, -- DBA_HIST view name
awr_location       IN VARCHAR2) -- AWR_ROOT or AWR_PDB
RETURN VARCHAR2;
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA_HIST%'
ORDER BY 1;

VIEW_NAME
--------------------------------
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_APPLY_SUMMARY
DBA_HIST_ASH_SNAPSHOT
...
DBA_HIST_WAITSTAT
DBA_HIST_WR_CONTROL
DBA_HIST_WR_SETTINGS

SELECT dbms_workload_repository.get_awr_view_name_by_location('DBA_HIST_WAITSTAT', 'AWR_ROOT')
FROM dual;

DBMS_WORKLOAD_REPOSITORY.GET_AWR_VIEW_NAME_BY_LOCATION('DBA_HIST_WAITSTAT','AWR_ROOT')
--------------------------------------------------------------------------------------
AWR_ROOT_WAITSTAT
 
LOAD
Imports AWR data from a previous EXTRACT file (see above) into the SYS schema dbms_workload_repository.load(
schname         IN VARCHAR2,
dmpfile         IN VARCHAR2 DEFAULT 'awrdat',
dmpdir          IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
new_dbid        IN NUMBER   DEFAULT NULL,
dup_snapshot_ok IN BOOLEAN  DEFAULT TRUE,
gather_stats    IN BOOLEAN  DEFAULT FALSE,
source_name     IN VARCHAR2 DEFAULT NULL,
logfile         IN VARCHAR2 DEFAULT NULL,
logdir          IN VARCHAR2 DEFAULT NULL);
exec dbms_workload_repository.load('XDB');
 
LOCAL_AWR_DBID
Returns the database id of the local workload repository dbms_workload_repository.local_awr_dbid RETURN NUMBER;
SELECT dbms_workload_repository.local_awr_dbid
FROM dual;

LOCAL_AWR_DBID
--------------
    1262297360
 
MODIFY_BASELINE_WINDOW_SIZE
Modifies the window size for the default moving window baseline

Installation default is 8 days		 dbms_workload_repository.modify_baseline_window_size(
window_size IN NUMBER,
dbid        IN NUMBER DEFAULT NULL);
set linesize 121
col baseline_name format a30

SELECT dbid, baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline;

exec dbms_workload_repository.modify_baseline_window_size(5);

SELECT dbid, baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline;

exec dbms_workload_repository.modify_baseline_window_size(8);
 
MODIFY_SNAPSHOT_SETTINGS
Modifies the interval between snapshots and/or the retention of snapshots in the repository

Overload 1

Note: Some of this functionality also exists in DBMS_MANAGEMENT_PACKS
MODIFY_AWR_SETTINGS proc		 dbms_workload_repository.modify_snapshot_settings(
retention IN NUMBER DEFAULT NULL,
interval  IN NUMBER DEFAULT NULL,
topnsql   IN NUMBER DEFAULT NULL,
dbid      IN NUMBER DEFAULT NULL);

Defaults
RETENTION 8 days = 10080 minutes
INTERVAL 60 minutes *
* Recommend this be reset to 15-20 min. maximum between snapshots
set linesize 121
col retention format a20
col snap_interval format a20

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control
WHERE dbid = (SELECT dbid FROM v$database);

SELECT dbid
FROM v$database;

exec dbms_workload_repository.modify_snapshot_settings((24*60*32), 20, 1000, 1701481905);

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control
WHERE dbid = (SELECT dbid FROM v$database);
Overload 2 dbms_workload_repository.modify_snapshot_settings(
retention IN NUMBER   DEFAULT NULL,
interval  IN NUMBER   DEFAULT NULL,
topnsql   IN VARCHAR2,
dbid      IN NUMBER   DEFAULT NULL);

Defaults
RETENTION 7 days = 10080 minutes
INTERVAL 60 minutes *
* Recommend this be reset to 15-30 min. maximum between snapshots
TOPNSQL
Users are allowed to specify the following values: ('DEFAULT', 'MAXIMUM', 'N')

Specifying 'DEFAULT' will revert the system back to the default behavior of Top 30 for level TYPICAL and Top 100 for level ALL.

Specifying 'MAXIMUM' will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number 'N' is equivalent to setting the Top N SQL with the NUMBER type.

Specifying 'N' will cause the system to flush the Top N SQL for each criteria. The 'N' string is converted into the number for Top N SQL.
set linesize 121
col retention format a20
col snap_interval format a20

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;

SELECT dbid
FROM v$database;

exec dbms_workload_repository.modify_snapshot_settings(14400, 20, 'MAXIMUM', 1701481905);

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;
 
MODIFY_TABLE_SETTINGS
Adjusts the settings of an AWR table dbms_workload_repository.modify_table_settings(
table_name  IN VARCHAR2,
dbid        IN NUMBER   DEFAULT NULL,
flush_level IN VARCHAR2 DEFAULT NULL); -- 'DEAFULT', 'TYPICAL' or 'ALL'
exec dbms_workload_repository.modify_table_settings('AWRDRPT_TEXT_TYPE_TABLE', NULL, 'ALL');
 
PURGE_SQL_DETAILS
Purges rows from the AWR SQL details tables
(WRH$_SQLTEXT and WHR$_SQL_PLAN) that are no longer required		 dbms_workload_repository.purge_sql_details(
numrows IN NUMBER DEFAULT NULL,
dbid    IN NUMBER DEFAULT NULL);
SELECT dbid FROM v$database;

exec dbms_workload_repository.purge_sql_details(10, 2497516142);
 
REGISTER_REMOTE_DATABASE
Register a remote database with AWR. The remote database must already be registered with UMF. The input node_id must be the UMF-assigned Node Id for the remote node.

Overload 1		 dbms_workload_repository.register_remote_database(node_id IN NUMBER);
TBD
Register a remote database with AWR, using the node_name for identity

Overload 2		 dbms_workload_repository.register_remote_database(
node_name     IN VARCHAR2,
topology_name IN VARCHAR2 DEFAULT NULL);
TBD
 
REMOVE_COLORED_SQL
Routine to remove a colored SQL ID. If an SQL ID is colored, it will always be captured in every snapshot, independent of its level of activities (i.e. does not have to be a TOP SQL) dbms_workload_repository.remove_colored_sql(
sql_id IN VARCHAR2,
dbid   IN NUMBER DEFAULT NULL);
See ADD_COLORED_SQL Demo Above
 
RENAME_BASELINE
Rename a SQL baseline dbms_workload_repository.rename_baseline(
old_baseline_name IN VARCHAR2,
new_baseline_name IN VARCHAR2,
dbid              IN NUMBER DEFAULT NULL);
SELECT dbid, baseline_name, baseline_type
FROM dba_hist_baseline;

exec dbms_workload_repository.rename_baseline('UW_BASE', 'UW_BASE2');

SELECT dbid, baseline_name, baseline_type
FROM dba_hist_baseline;

exec dbms_workload_repository.rename_baseline('UW_BASE', 'UW_BASE');
 
SELECT_BASELINE_DETAILS
Display baseline statistics dbms_workload_repository.select_baseline_metrics(
l_baseline_id IN NUMBER,
l_beg_snap    IN NUMBER DEFAULT NULL,
l_end_snap    IN NUMBER DEFAULT NULL,
l_dbid        IN NUMBER DEFAULT NULL)
RETURN awrbl_details_type_table PIPELINED;
SELECT dbid, baseline_id, baseline_name, baseline_type
FROM dba_hist_baseline;

set linesize 121
col start_snap_time format a30
col end_snap_time format a30

SELECT *
FROM TABLE(dbms_workload_repository.select_baseline_details(0));
 
SELECT_BASELINE_METRIC
Display metric stats for a baseline dbms_workload_repository.select_baseline_metric(
l_baseline_name IN VARCHAR2,
l_dbid          IN NUMBER DEFAULT NULL,
l_instance_num  IN NUMBER DEFAULT NULL)
RETURN awrbl_metric_type_table PIPELINED;
SELECT dbid, baseline_id, baseline_name, baseline_type
FROM dba_hist_baseline;

set pagesize 0
set linesize 121

SELECT *
FROM TABLE(dbms_workload_repository.select_baseline_metric(0));
 
UNREGISTER_REMOTE_DATABASE
Unregisters a remote database from the AWR. The input node ID must be the UMF Node ID of the remote node

Overload 1		 dbms_workload_repository.unregister_remote_database(
node_id      IN NUMBER,
remote_check IN BOOLEAN DEFAULT TRUE);
TBD
Overload 2 dbms_workload_repository.unregister_remote_database(
node_name     IN VARCHAR2,
topology_name IN VARCHAR2 DEFAULT NULL,
remote_check  IN BOOLEAN  DEFAULT TRUE);
TBD
 
UPDATE_DATAFILE_INFO
Updates WRH$_DATAFILE rows for the datafile name and tablespace name dbms_workload_repository.update_tablespace_info;
exec dbms_workload_repository.update_tablespace_info;
 
UPDATE_OBJECT_INFO
Updates rows of WRH$_SEG_STAT_OBJ table that represent objects in the local database dbms_workload_repository.update_object_info(maxrows IN NUMBER DEFAULT 0);
exec dbms_workload_repository.update_object_info(120000);

Related Topics
Built-in Functions
Built-in Packages
Active Session History
ASH Report
AWRINFO_UTIL
AWR Report
DBMS_AUTO_SQLSET
DBMS_AWRHUB_SERVER
DBMS_AWRHUB_SOURCE
DBMS_AWR_ANALYTICS
DBMS_AWR_REPORT_LAYOUT
DBMS_MANAGEMENT_BOOTSTRAP
DBMS_MANAGEMENT_PACKS
DBMS_SWRF_INTERNAL
Files of Interest
Startup Parameters
StatsPack
What's New In 21c
What's New In 23c

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