Oracle DBMS_SWRF_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Internal support package for AWR functionality.
AUTHID DEFINER
Dependencies
ALL_TABLES DBMS_LOCK KU$_JOBDESC
ALL_TAB_COLUMNS DBMS_MANAGEMENT_PACKS KU$_JOBSTATUS
AWR_EXPORT_DUMP_ID_TYPE DBMS_OUTPUT KU$_LOGENTRY
AWR_OBJECT_INFO_TYPE DBMS_SCHEDULER KU$_LOGLINE1010
DBA_ADVISOR_PARAMETERS DBMS_STANDARD KU$_STATUS
DBA_ADVISOR_TASKS DBMS_STATS PLITBLM
DBA_CONSTRAINTS DBMS_SWRF_LIB UTL_FILE
DBA_CONS_COLUMNS DBMS_SWRF_REPORT_INTERNAL V$DATABASE
DBA_DIRECTORIES DBMS_SYSTEM V$TIMER
DBA_HIST_WR_SETTINGS DBMS_UTILITY WRM$_BASELINE
DBA_TABLES DBMS_WORKLOAD_CAPTURE WRM$_BASELINE_DETAILS
DBA_TAB_COLUMNS DBMS_WORKLOAD_REPLAY WRM$_DATABASE_INSTANCE
DBA_USERS DBMS_WORKLOAD_REPOSITORY WRM$_SNAPSHOT
DBMS_ADDM DBMS_WRR_INTERNAL WRM$_WR_CONTROL
DBA_ASSERT DBMS_XPLAN X$KEWRTB
DBMS_DATAPUMP DUAL  
First Available Not known
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsawri.plb
Subprograms
AWR_CLEAN DEFAULT_REPORT_DBID RESERVE_SNAPSHOT_RANGE
AWR_DECODE_OBJECT_TYPE DUMP_COLUMN_STATS RESET_AWR
AWR_EXTRACT GET_AWR_DBID RESET_DEFAULT_TZ
AWR_FLUSH_T2SS_SNAP GET_DUMP_INFO SET_AWR_DBID
AWR_GET_DDL GET_SOURCE_NAME SET_SOURCE_NAME
AWR_GET_MASTER INSERT_BASELINE_DETAILS T2S_REGISTER_NFY
AWR_GET_OBJECT_INFO MASSAGE_COLUMN_STATS T2S_SNAP_INTENT_NFY
AWR_LOAD MODIFY_AUTOPURGE_SETTINGS UNREGISTER_DATABASE
AWR_LOAD2 MOVE_TO_AWR UNREGISTER_REMOTE_DATABASE
BASELINE_MIGRATE REGISTER_DATABASE UNRESERVE_SNAPSHOT_RANGE
BLUPDATE_LAST_TIME_COMPUTED REGISTER_LOCAL_DBID UPDATE_DATAFILE_INFO
CLEANUP_DATABASE REGISTER_REMOTE_DATABASE UPDATE_OBJECT_INFO
CLEAR_AWR_DBID REMOVE_ADDM_TASKS UPGRADE_SCHEMA
CREATE_IMPORT_PARTITIONS REMOVE_STAGING_SCHEMA VALIDATE_AWR_LOCATION
CREATE_STAGING_SCHEMA REMOVE_WR_CONTROL  
 
AWR_CLEAN
Undocumented dbms_swrf_internal.awr_clean(schname IN VARCHAR2);
conn sys@pdbdev as sysdba

exec dbms_swrf_internal.awr_clean('SCOTT');
 
CLEAR_AWR_DBID
Undocumented dbms_swrf_internal.clear_awr_dbid;
exec dbms_swrf_internal.clear_awr_dbid;
 
CREATE_IMPORT_PARTITIONS
Undocumented dbms_swrf_internal.create_import_partitions(
dbid           IN NUMBER,
low_snap_id    IN NUMBER,
high_snap_id   IN NUMBER,
low_snap_time  IN DATE,
high_snap_time IN DATE);
TBD
 
CREATE_STAGING_SCHEMA
Undocumented

A 10046 trace reveals that it can build 22 WRH$ tables
dbms_swrf_internal.create_staging_schema;
SQL> SELECT MAX(last_ddl_time) FROM dba_objects;

MAX(LAST_DDL_TIME)
--------------------
10-AUG-2017 17:55:01

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
19-JUL-2017 11:21:52

SQL> exec dbms_swrf_internal.create_staging_schema;

PL/SQL procedure successfully completed.

SQL> SELECT MAX(last_ddl_time) FROM dba_objects;

MAX(LAST_DDL_TIME)
--------------------
10-AUG-2017 17:55:01

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
19-JUL-2017 11:21:52

SQL> exec dbms_swrf_internal.remove_staging_schema;

PL/SQL procedure successfully completed.

SQL> SELECT MAX(last_ddl_time) FROM dba_objects;

MAX(LAST_DDL_TIME)
--------------------
10-AUG-2017 17:55:01

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
19-JUL-2017 11:21:52
 
DEFAULT_REPORT_DBID (new 12.2)
Returns the DBID of the current database dbms_swrf_internal.default_report_dbid RETURN NUMBER;
SQL> SELECT dbms_swrf_internal.default_report_dbid
  2  FROM dual;

DEFAULT_REPORT_DBID
-------------------
          549318987
 
GET_AWR_DBID
Returns the CDB$ROOT DBID dbms_swrf_internal.get_awr_dbid RETURN NUMBER;
SELECT dbid
FROM v$database;

DECLARE
l_dbid NUMBER := dbms_swrf_internal.get_awr_dbid;
BEGIN
  dbms_output.put_line(l_dbid);
END;
/
 
GET_SOURCE_NAME
Undocumented dbms_swrf_internal.get_source_name RETURN VARCHAR2;
See SET_SOURCE_NAME Demo Below
 
INSERT_BASELINE_DETAILS
Undocumented dbms_swrf_internal.insert_baseline_details;
exec dbms_swrf_internal.insert_baseline_details;
 
MODIFY_AUTOPURGE_SETTINGS
Undocumented dbms_swrf_internal.modify_autopurge_settings(
dbid      IN NUMBER,
purge_val IN BOOLEAN);
SELECT dbid
FROM v$database;

      DBID
----------
1933765913

exec dbms_swrf_internal.modify_autopurge_settings(1933765913, TRUE);
 
REGISTER_DATABASE
Undocumented dbms_swrf_internal.grant_register_database(dbid IN NUMBER);
exec dbms_swrf_internal.register_database(dbms_swrf_internal.get_awr_dbid);
 
REGISTER_LOCAL_DBID
Undocumented dbms_swrf_internal.register_local_dbid;
exec dbms_swrf_internal.register_local_dbid;
 
REGISTER_REMOTE_DATABASE
Undocumented dbms_swrf_internal.remove_staging_schema;
exec dbms_swrf_internal.remove_staging_schema;
 
REMOVE_ADDM_TASKS (new 12.2)
Drops all ADDM tasks dbms_swrf_internal.remove_addm_tasks(dbid IN NUMBER);
SQL> SELECT dbid FROM v$database;

      DBID
----------
 549318987

exec dbms_swrf_internal.remove_addm_tasks(549318987);
 
REMOVE_STAGING_SCHEMA
Undocumented dbms_swrf_internal.remove_staging_schema;
See CREATE_STAGING_SCHEMA Demo Above
 
RESERVE_SNAPSHOT_RANGE
Undocumented dbms_swrf_internal.reserve_snapshot_range(
start_snap_id IN NUMBER,
end_snap_id   IN NUMBER,
usr_prfx      IN VARCHAR2,
dbid          IN NUMBER);
TBD
 
RESET_AWR (new 12.2)
Resets the AWR and starts a new incarnation of snap identifiers dbms_swrf_internal.reset_awr(
dbid         IN NUMBER,
reset_mode   IN VARCHAR2,
next_snap_id IN NUMBER);
The Library will not be publishing an example of this as it has the potential to be destructive.
 
RESET_DEFAULT_TZ
Undocumented dbms_swrf_internal.reset_default_tz;
exec dbms_swrf_internal.reset_default_tz;
 
UNREGISTER_DATABASE
Undocumented dbms_swrf_internal.unregister_database(dbid IN NUMBER)
exec dbms_swrf_internal.unregister_database(dbms_swrf_internal.get_awr_dbid);
 
UNRESERVE_SNAPSHOT_RANGE
Undocumented dbms_swrf_internal.unreserve_snapshot_range(
start_snap_id IN NUMBER,
end_snap_id   IN NUMBER,
usr_prfx      IN VARCHAR2,
dbid          IN NUMBER);
TBD
 
UPDATE_DATAFILE_INFO
Undocumented dbms_swrf_internal.update_datafile_info;
exec dbms_swrf_internal.update_datafile_info;
 
VALIDATE_AWR_LOCATION (new 12.2)
Returns TRUE if the string parameter corresponds with the AWR's location dbms_swrf_internal.validate_awr_location(awr_location IN VARCHAR2)
RETURN BOOLEAN;
DECLARE
 awrloc VARCHAR2(30) := 'SYSAUX';
BEGIN
  IF dbms_swrf_internal.validate_awr_location(awrloc) THEN
    dbms_output.put_line('The AWR location is ' || awrloc);
  ELSE
    dbms_output.put_line('The AWR is not located at ' || awrloc);
  END IF;
END;
/

Related Topics
AWR Report
Built-in Functions
Built-in Packages
DBMS_WORKLOAD_REPOSITORY
What's New In 12cR1
What's New In 12cR2

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