Oracle | DBMS_SWRF

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