Oracle | DBMS

Oracle DBMS_SWAT (Synonym DBMS_AUTO_MV) 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 Utilities that manage automatically created, think autonomous self-tuning, materialized views.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
DEFAULT_NUM_VALUE	VARCHAR2(1)	NULL;
DEFAULT_STR_VALUE	INTEGER	NULL

Dependencies

ALL_MVIEWS	DBMS_STANDARD	DBMS_SWAT_MM_INTERNAL
ALL_USERS	DBMS_STATS_INTERNAL	DBMS_SWAT_VER_INTERNAL
DBMS_AUTO_MV	DBMS_SWAT_ARM_INTERNAL	DBMS_SYS_ERROR
DBMS_AUTO_TASK_ADMIN	DBMS_SWAT_LIB

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-38133	Invalid parameter name
ORA-38134	Invalid parameter value

First Available 20c

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to the DBA role

Source {ORACLE_HOME}/rdbms/admin/dbmsswat.sql

Subprograms

CONFIGURE

DROP_AUTO_MVS

CONFIGURE (new 21c)

Sets a string SWAT system configuration parameter

Overload 1 dbms_swat.configure( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, allow IN BOOLEAN := TRUE);

col parameter_name format a35

      col parameter_str_value format a20

      

      SELECT parameter_name, parameter_str_value

      FROM  swat$config_params

      WHERE parameter_name LIKE 'AUTO%'

      AND parameter_str_value IS NOT NULL

      ORDER BY 1;

      

      PARAMETER_NAME                      PARAMETER_STR_VALUE

      ----------------------------------- -------------------

      AUTO_MV_MAINT_TASK                  DISABLE

      AUTO_MV_MODE                        OFF

      AUTO_MV_SPACE_BUDGET                10%

      AUTO_ZMAP_MODE                      OFF

      
      

      exec dbms_swat.configure('AUTO_MV_SPACE_BUDGET', 
      '15%', TRUE);

      

      PL/SQL procedure successfully completed.

Sets a numeric SWAT system configuration parameter

Overload 2 dbms_swat.configure( parameter_name IN VARCHAR2, parameter_value IN NUMBER, allow IN BOOLEAN := TRUE);

col parameter_name format a35

      col parameter_str_value format a20

      

      SELECT parameter_name, parameter_num_value

      FROM  swat$config_params

      WHERE parameter_name LIKE 'AUTO%'

      AND parameter_num_value IS NOT NULL

      ORDER BY 1;

      

      PARAMETER_NAME                      PARAMETER_NUM_VALUE

      ----------------------------------- -------------------

      AUTO_MV_ANALYZE_REPORT_RETENTION                     31

      AUTO_MV_ANALYZE_WORKLOAD_MIN_TIME                   120

      AUTO_MV_ANALYZE_WORKLOAD_WINDOW                      24

      AUTO_MV_MAINT_REPORT_RETENTION                       31

      AUTO_MV_RETENTION                                   373

      AUTO_MV_VERIFY_REPORT_RETENTION                      31

      AUTO_ZMAP_TASK_REPORT_RETENTION                      30

      AUTO_ZMAP_TASK_RUN_TIME                             180

      AUTO_ZMAP_TASK_WAIT                                  60

      AUTO_ZMAP_UNUSED_EXPIRY                              30

      

      exec dbms_swat.configure('AUTO_MV_ANALYZE_REPORT_RETENTION', 
      32, TRUE);

      

      PL/SQL procedure successfully completed.

DROP_AUTO_MVS (new 21c)

Drops an auto_mvs created by the auto MV task dbms_swat.drop_auto_mvs( owner IN VARCHAR2, mv_name IN VARCHAR2, allow_recreate IN BOOLEAN DEFAULT FALSE);

TBD