Oracle DBMS_SWRF_INTERNAL
Version 18.3

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 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 18c
What's New In 19c

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