Oracle | DBMS

Oracle DBMS_SWAT (Synonym DBMS_AUTO_MV) 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 OATS Object Activity Tracking System - dbmsoats.sql
SQL Workload Analysis & Tuning
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
{ORACLE_HOME}/rdbms/admin/prvtswat.plb

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