Oracle DBMS_SWRF_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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