Oracle DBMS_SWRF_INTERNAL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose Internal support package for AWR functionality.
AUTHID DEFINER
Dependencies
ALL_TABLES DBMS_DATAPUMP KU$_JOBSTATUS
ALL_TAB_COLUMNS DBMS_LOCK KU$_LOGENTRY
AWR_EXPORT_DUMP_ID_TYPE DBMS_MANAGEMENT_PACKS KU$_LOGLINE
AWR_OBJECT_INFO_TYPE DBMS_OUTPUT KU$_LOGLINE1010
DBA_ADVISOR_PARAMETERS DBMS_SCHEDULER KU$_STATUS
DBA_ADVISOR_TASKS DBMS_STANDARD PLITBLM
DBA_CONSTRAINTS DBMS_STATS UTL_FILE
DBA_CONS_COLUMNS DBMS_SWRF_LIB V$DATABASE
DBA_DIRECTORIES DBMS_SWRF_REPORT_INTERNAL V$TIMER
DBA_HIST_WR_SETTINGS DBMS_SYSTEM WRM$_BASELINE
DBA_TABLES DBMS_UTILITY WRM$_BASELINE_DETAILS
DBA_TAB_COLUMNS DBMS_WORKLOAD_REPOSITORY WRM$_DATABASE_INSTANCE
DBA_USERS DBMS_XPLAN WRM$_SNAPSHOT
DBMS_ADDM DUAL WRM$_WR_CONTROL
DBA_ASSERT KU$_JOBDESC X$KEWRTB
First Available Not known
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsawri.plb
Subprograms
AWR_CHECK_DMP_SNAPID CREATE_UPGRADE_PARTITIONS RESERVE_SNAPSHOT_RANGE
AWR_CLEAN DEFAULT_REPORT_DBID RESET_AWR
AWR_DECODE_OBJECT_TYPE DUMP_COLUMN_STATS RESET_DEFAULT_TZ
AWR_EXTRACT GET_AWR_DBID SET_AWR_DBID
AWR_FLUSH_T2SS_SNAP GET_DUMP_INFO SET_SOURCE_NAME
AWR_GET_DDL GET_SOURCE_NAME T2S_REGISTER_NFY
AWR_GET_MASTER INSERT_BASELINE_DETAILS T2S_SNAP_INTENT_NFY
AWR_GET_OBJECT_INFO MASSAGE_COLUMN_STATS UNREGISTER_DATABASE
AWR_LOAD MODIFY_AUTOPURGE_SETTINGS UNREGISTER_REMOTE_DATABASE
AWR_LOAD2 MOVE_TO_AWR UNRESERVE_SNAPSHOT_RANGE
BASELINE_MIGRATE REGISTER_DATABASE UPDATE_DATAFILE_INFO
BLUPDATE_LAST_TIME_COMPUTED REGISTER_LOCAL_DBID UPDATE_OBJECT_INFO
CLEANUP_DATABASE REGISTER_REMOTE_DATABASE UPGRADE_SCHEMA
CLEAR_AWR_DBID REMOVE_ADDM_TASKS VALIDATE_AWR_LOCATION
CREATE_IMPORT_PARTITIONS REMOVE_STAGING_SCHEMA WAIT_FOR_AWR_OPEN
CREATE_STAGING_SCHEMA REMOVE_WR_CONTROL  
 
AWR_CHECK_DMP_SNAPID (new 18c)
Undocumented dbms_swrf_internal.awr_check_dmp_snapid(
schname  IN VARCHAR2,
new_dbid IN NUMBER);
TBD
 
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)
--------------------
12-APR-2019 22:00:23

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
23-FEB-2019 16:00:54

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)
--------------------
12-APR-2019 22:06:01

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
23-FEB-2019 16:00:54

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)
--------------------
12-APR-2019 22:06:31

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
23-FEB-2019 16:00:54
 
CREATE_UPGRADE_PARTITIONS (new 18c)
Undocumented dbms_swrf_internal.create_upgrade_partitions;
exec dbms_swrf_internal.create_upgrade_partitions;
 
DEFAULT_REPORT_DBID
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
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
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
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 in ' || awrloc);
  END IF;
END;
/
The AWR is not located in SYSAUX
 
WAIT_FOR_AWR_OPEN (new 18c)
Undocumented dbms_swrf_internal.wait_for_awr_open(timeout IN NUMBER)
RETURN BOOLEAN;
BEGIN
  IF dbms_swrf_internal.wait_for_awr_open(10) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T

Related Topics
AWR Report
Built-in Functions
Built-in Packages
DBMS_WORKLOAD_REPOSITORY
What's New In 18cR3
What's New In 19cR2

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