Oracle DBMS_SWRF_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
{ORACLE_HOME}/rdbms/admin/prvtawri.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');
 
AWR_DECODE_OBJECT_TYPE
Undocumented dbms_swrf_internal.awr_decode_object_type(
type_id    IN NUMBER,
max_length IN NUMBER)
RETURN VARCHAR2;
TBD
 
AWR_EXP
Undocumented dbms_swrf_internal.awr_exp(
dmpfile IN VARCHAR2
dmpdir  IN VARCHAR2
dbid    IN NUMBER
bid     IN NUMBER
eid     IN NUMBER
mbtype  IN NUMBER
mbcred  IN VARCHAR2
dop     IN NUMBER);
TBD
 
AWR_EXTRACT
Undocumented dbms_swrf_internal.awr_extract(
dmpfile          IN VARCHAR2,
dmpdir           IN VARCHAR2,
bid              IN NUMBER,
eid              IN NUMBER,
dbid             IN NUMBER,
from_source_name IN VARCHAR2,
to_source_name   IN VARCHAR2,
logdir           IN VARCHAR2);
TBD
 
AWR_FLUSH_T2SS_SNAP
Undocumented dbms_swrf_internal.awr_flush_t2ss_snap(
dbid             IN NUMBER,
snap_id          IN NUMBER,
srcdbid          IN NUMBER,
flush_level      IN VARCHAR2,
topology_name    IN VARCHAR2,
topology_version IN NUMBER);
TBD
 
AWR_GET_DDL
Undocumented dbms_swrf_internal.awr_get_ddl(
dmpfile IN VARCHAR2,
dmpdir  IN VARCHAR2,
logdir  IN VARCHAR2,
sqlfile IN VARCHAR2;
TBD
 
AWR_GET_MASTER
Undocumented dbms_swrf_internal.awr_get_master(
dmpfile IN  VARCHAR2,
dmpdir  IN  VARCHAR2,
logdir  IN  VARCHAR2,
master  OUT VARCHAR2);
TBD
 
AWR_GET_OBJECT_INFO
Undocumented dbms_swrf_internal.awr_get_object_info(
con_dbid  IN NUMBER,
tsid      IN NUMBER,
objid     IN NUMBER,
dataobjid IN NUMBER,
current   IN sys.awr_object_info_type)
RETURN  sys.awr_object_info_type;
TBD
 
AWR_IMP
Undocumented dbms_swrf_internal.awr_imp(
dmpfile  IN VARCHAR2,
dmpdir   IN VARCHAR2,
new_dbid IN NUMBER,
mbtype   IN NUMBER,
mbcred   IN VARCHAR2,
dop      IN NUMBER);
TBD
 
AWR_LOAD
Undocumented dbms_swrf_internal.awr_load(
schname IN VARCHAR2,
dmpfile IN VARCHAR2,
dmpdir  IN VARCHAR2,
logdir  IN VARCHAR2);
TBD
 
AWR_LOAD2
Undocumented dbms_swrf_internal.awr_load2(
schname IN  VARCHAR2,
dmpfile IN  VARCHAR2,
dmpdir  IN  VARCHAR2,
logdir  IN  VARCHAR2,
dumpid  OUT sys.awr_export_dump_id_type);
TBD
 
BASELINE_MIGRATE
Undocumented dbms_swrf_internal.baseline_migrate(
migrate_tables IN BOOLEAN,
drop_tables    IN BOOLEAN);
exec dbms_swrf_internal.baseline_migrate(FALSE, FALSE);

PL/SQL procedure successfully completed.
 
BLUPDATE_LAST_TIME_COMPUTED
Undocumented dbms_swrf_internal.blupdate_last_time_computed(
baseline_id IN NUMBER,
last_time   IN DATE,
dbid        IN NUMBER);
TBD
 
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_SWRF_REPORT_INTERNAL
DBMS_WORKLOAD_REPOSITORY
What's New In 21c
What's New In 23c

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