Oracle DBMS_SWRF_INTERNAL
Version 21c

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_LOB KU$_LOGENTRY
AWRRPT_VARCHAR256_LIST_TYPE DBMS_LOCK KU$_LOGLINE
AWR_EXPORT_DUMP_ID_TYPE DBMS_MANAGEMENT_PACKS KU$_LOGLINE1010
AWR_OBJECT_INFO_TYPE DBMS_OUTPUT KU$_STATUS
DBA_ADVISOR_PARAMETERS DBMS_SCHEDULER PLITBLM
DBA_ADVISOR_TASKS DBMS_STANDARD UTL_FILE
DBA_CONSTRAINTS DBMS_STATS V$INSTANCE
DBA_CONS_COLUMNS DBMS_SWRF_LIB V$TIMER
DBA_DIRECTORIES DBMS_SWRF_REPORT_INTERNAL WRM$_DATABASE_INSTANCE
DBA_HIST_WR_SETTINGS DBMS_SYSTEM WRM$_SNAPSHOT
DBA_TABLES DBMS_SYS_ERROR WRM$_WR_CONTROL
DBA_TAB_COLUMNS DBMS_UTILITY WRM$_WR_SETTINGS
DBA_USERS DBMS_WORKLOAD_REPOSITORY X$KEWRTB
DBMS_ADDM DBMS_XPLAN XMLTYPE
DBA_ASSERT DUAL XQSEQUENCE
DBMS_AWRWH_LIB KU$_JOBDESC  
First Available Not known
Security Model Owned by SYS with EXECUTE granted to the SYSUMF role.
Source {ORACLE_HOME}/rdbms/admin/prvsawri.plb
Subprograms
AWR_CHECK_DMP_SNAPID DEFAULT_REPORT_DBID REGISTER_REMOTE_DATABASE
AWR_CLEAN DISABLE_LRGTEST REMOVE_ADDM_TASKS
AWR_DECODE_OBJECT_TYPE DROP_DIR_OBJ REMOVE_WR_CONTROL
AWR_EXP DUMP_COLUMN_STATS RESERVE_SNAPSHOT_RANGE
AWR_EXTRACT ENABLE_LRGTEST RESET_AWR
AWR_FLUSH_T2SS_SNAP GET_AWR_DBID RESET_DEFAULT_TZ
AWR_GET_DDL GET_DEFAULT_AWR_DBID RUN_DATAPUMP_JOB
AWR_GET_MASTER GET_DUMP_INFO SET_AWR_DBID
AWR_GET_OBJECT_INFO GET_SOURCE_NAME SET_SOURCE_NAME
AWR_IMP INSERT_BASELINE_DETAILS T2S_REGISTER_NFY
AWR_LOAD LOCAL_AWR_DBID T2S_SNAP_INTENT_NFY
AWR_LOAD2 MASSAGE_COLUMN_STATS UNREGISTER_DATABASE
BASELINE_MIGRATE MODIFY_AUTOPURGE_SETTINGS UNREGISTER_REMOTE_DATABASE
BLUPDATE_LAST_TIME_COMPUTED MOVE_TO_AWR UNRESERVE_SNAPSHOT_RANGE
CLEANUP_CATALOG PRINT_LRGTEST_TABLES UPDATE_DATAFILE_INFO
CLEANUP_DATABASE REDEFINE_PARTITIONS UPDATE_OBJECT_INFO
CLEAR_AWR_DBID REFRESH_CATALOG UPGRADE_SCHEMA
CREATE_DIR_OBJ REGISTER_DATABASE VALIDATE_AWR_LOCATION
CREATE_IMPORT_PARTITIONS REGISTER_LOCAL_DBID WAIT_FOR_AWR_OPEN
 
AWR_CHECK_DMP_SNAPID
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');
 
CLEANUP_CATALOG
Undocumented dbms_swrf_internal.cleanup_catalog;
exec dbms_swrf_internal.cleanup_catalog;

PL/SQL procedure successfully completed.
 
CLEANUP_DATABASE
Undocumented

Possibly only FALSE is supported
dbms_swrf_internal.cleanup_databasecleanup_local IN BOOLEAN);
exec dbms_swrf_internal.cleanup_database(FALSE);

PL/SQL procedure successfully completed.

exec dbms_swrf_internal.cleanup_database(TRUE);
BEGIN dbms_swrf_internal.cleanup_database(TRUE); END;
*
ERROR at line 1:
ORA-13521: Unregister operation on local Database id (1863203691) not allowed
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 414
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 447
ORA-06512: at line 1
 
CLEAR_AWR_DBID
Undocumented dbms_swrf_internal.clear_awr_dbid;
exec dbms_swrf_internal.clear_awr_dbid;

PL/SQL procedure successfully completed.
 
CREATE_DIR_OBJ (new 21c)
Undocumented dbms_swrf_internal.create_dir_obj(
dir_obj  IN VARCHAR2,
dir_path IN VARCHAR2);
SELECT COUNT(*)
FROM dba_directories
WHERE directory_name = 'SWRF';

 COUNT(*)
---------
        0


exec dbms_swrf_internal.create_dir_obj('SWRF', '/home/oracle/');

SELECT COUNT(*)
FROM dba_directories
WHERE directory_name = 'SWRF';

 COUNT(*)
---------
        1
 
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
 
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
 
DISABLE_LRGTEST
Undocumented

Likely must point to one of a hard coded set of AWR linked tables
dbms_swrf_internal.disable_lrgtest(tbname IN VARCHAR2);
exec dbms_swrf_internal.disable_lrgtest('WRH$_AWR_TEST_1');

PL/SQL procedure successfully completed.
 
DROP_DIR_OBJ (new 21c)
Undocumented dbms_swrf_internal.drop_dir_obj(dir_obj IN VARCHAR2);
CREATE DIRECTORY swrf AS '/home/oracle';

SELECT COUNT(*)
FROM dba_directories
WHERE directory_name = 'SWRF';

 COUNT(*)
---------
        1


exec dbms_swrf_internal.drop_dir_obj('SWRF');

PL/SQL procedure successfully completed.

SELECT COUNT(*)
FROM dba_directories
WHERE directory_name = 'SWRF';

 COUNT(*)
---------
        0
 
ENABLE_LRGTEST
Undocumented

Likely must point to one of a hard coded set of AWR linked tables
dbms_swrf_internal.enable_lrgtest(tbname IN VARCHAR2);
exec dbms_swrf_internal.enable_lrgtest('WRH$_AWR_TEST_1');

PL/SQL procedure successfully completed.
 
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_DUMP_INFO
Undocumented dbms_swrf_internal.get_dump_info(
master_name IN  VARCHAR2
dump_id     OUT sys.awr_export_dump_id_type,
fp          IN  sys.utl_file.file_type);
TBD
 
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;
 
LOCAL_AWR_DBID
Undocumented dbms_swrf_internal.local_awr_dbid RETURN NUMBER;
SELECT dbms_swrf_internal.local_awr_dbid
FROM dual;

LOCAL_AWR_DBID
--------------
    1863203691
 
MODIFY_AUTOPURGE_SETTINGS
Undocumented dbms_swrf_internal.modify_autopurge_settings(
dbid      IN NUMBER,
purge_val IN BOOLEAN);
SELECT dbid
FROM v$database;

      DBID
----------
1863203691


exec dbms_swrf_internal.modify_autopurge_settings(1863203691, TRUE);

PL/SQL procedure successfully completed.
 
REDEFINE_PARTITIONS (new 21c)
Undocumented dbms_swrf_internal.redefine_partitions(
dbid    IN NUMBER,
tbid    IN NUMBER,
timeout IN NUMBER);
TBD
 
REFRESH_CATALOG
Undocumented dbms_swrf_internal.refresh_catalog;
exec dbms_swrf_internal.refresh_catalog;

PL/SQL procedure successfully completed.
 
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;
 
REMOVE_ADDM_TASKS
Drops all ADDM tasks dbms_swrf_internal.remove_addm_tasks(dbid IN NUMBER);
SELECT dbid FROM v$database;

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


exec dbms_swrf_internal.remove_addm_tasks(549318987);
 
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
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 19c
What's New In 20c-21c

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