Oracle DBMS_SWRF_INTERNAL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Internal support package for AWR functionality.
AUTHID DEFINER
Dependencies
ALL_TABLES DBMS_MANAGEMENT_PACKS KU$_JOBSTATUS
ALL_TAB_COLUMNS DBMS_OUTPUT KU$_LOGENTRY
AWRBL_DETAILS_TYPE_TABLE DBMS_SCHEDULER KU$_LOGLINE1010
AWR_EXPORT_DUMP_ID_TYPE DBMS_STANDARD KU$_STATUS
AWR_OBJECT_INFO DBMS_STATS PLITBLM
DBA_CONSTRAINTS DBMS_SWRF_LIB UTL_FILE
DBA_CONS_COLUMNS DBMS_SYSTEM V$DATABASE
DBA_DIRECTORIES DBMS_UTILITY V$TIMER
DBA_TABLES DBMS_WORKLOAD_CAPTURE WRM$_BASELINE
DBA_TAB_COLUMNS DBMS_WORKLOAD_REPLAY WRM$_BASELINE_DETAILS
DBA_USERS DBMS_WORKLOAD_REPOSITORY WRM$_DATABASE_INSTANCE
DBA_ASSERT DBMS_WRR_INTERNAL WRM$_SNAPSHOT
DBMS_DATAPUMP DUAL WRM$_WR_CONTROL
DBMS_LOCK 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_CLEAN CREATE_STAGING_SCHEMA REMOVE_STAGING_SCHEMA
AWR_DECODE_OBJECT_TYPE CREATE_SYSAWR_USER REMOVE_WR_CONTROL
AWR_EXTRACT DUMP_COLUMN_STATS RESERVE_SNAPSHOT_RANGE
AWR_FLUSH_T2SS_SNAP GET_AWR_DBID RESET_DEFAULT_TZ
AWR_GET_DDL GET_DUMP_INFO SET_AWR_DBID
AWR_GET_MASTER GET_SOURCE_NAME SET_SOURCE_NAME
AWR_GET_OBJECT_INFO GRANT_PRIV_STAGING_SCHEMA T2S_REGISTER_NFY
AWR_LOAD INSERT_BASELINE_DETAILS 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_DATABASE REGISTER_DATABASE UPDATE_DATAFILE_INFO
CLEAR_AWR_DBID REGISTER_LOCAL_DBID UPDATE_OBJECT_INFO
CREATE_IMPORT_PARTITIONS REGISTER_REMOTE_DATABASE UPGRADE_SCHEMA
 
AWR_CLEAN
Undocumented dbms_swrf_internal.awr_clean(schname IN VARCHAR2);
conn sys@pdborcl 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 (new 12.1)
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-2013 17:55:01

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
19-JUL-2013 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-2013 17:55:01

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
19-JUL-2013 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-2013 17:55:01

SQL> SELECT MAX(created) FROM dba_users;

MAX(CREATED)
--------------------
19-JUL-2013 11:21:52
 
CREATE_SYSAWR_USER
Undocumented dbms_swrf_internal.create_sysawr_user;
exec dbms_swrf_internal.create_sysawr_user;
 
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
 
GRANT_PRIV_STAGING_SCHEMA
Undocumented dbms_swrf_internal.grant_priv_staging_schema;
exec dbms_swrf_internal.grant_priv_staging_schema;
 
INSERT_BASELINE_DETAILS
Undocumented dbms_swrf_internal.insert_baseline_details;
exec dbms_swrf_internal.insert_baseline_details;
 
MODIFY_AUTOPURGE_SETTINGS (new 12.1)
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_STAGING_SCHEMA
Undocumented dbms_swrf_internal.remove_staging_schema;
See CREATE_STAGING_SCHEMA Demo Above
 
RESERVE_SNAPSHOT_RANGE (new 12.1)
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_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 (new 12.1)
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 (new 12.1)
Undocumented dbms_swrf_internal.update_datafile_info;
exec dbms_swrf_internal.update_datafile_info;

Related Topics
AWR Report
DBMS_WORKLOAD_REPOSITORY
Packages

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