Oracle | DBMS

Oracle DBMS_SWAT (Synonym DBMS_AUTO_MV) Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Subprograms for the management of automatic 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_UTILS
ALL_USERS	DBMS_STATS_INTERNAL	DBMS_SWAT_VER_INTERNAL
DBA_SQLSET	DBMS_SUMA_LIB	DBMS_SYS_ERROR
DBMS_AUTO_MV	DBMS_SWAT_ARM_INTERNAL	PRVT_REPORT_TAGS
DBMS_AUTO_TASK_ADMIN	DBMS_SWAT_INTERNAL	SWAT_VER$
DBMS_REPORT	DBMS_SWAT_MM_INTERNAL	XMLTYPE
DBMS_SPM_INTERNAL

Documented Yes: Packages and Types ReferencePL/SQL 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/prvsswat.plb
{ORACLE_HOME}/rdbms/admin/prvtswat.plb

Subprograms

CONFIGURE	RECOMMEND	REPORT_ACTIVITY
DROP_AUTO_MVS	REFRESH	REPORT_LAST_ACTIVITY

CONFIGURE

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

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

RECOMMEND (new 23ai)

Undocumented dbms_swat.recommend( sts_owner IN VARCHAR2, sts_name IN VARCHAR2, workload_start_time IN TIMESTAMP, workload_end_time IN TIMESTAMP, automv_mode IN VARCHAR2);

TBD

REFRESH (new 23ai)

Undocumented dbms_swat.refresh;

TBD

REPORT_ACTIVITY (new 23ai)

Undocumented dbms_swat.report_activity( activity_start IN TIMESTAMP WITH TIME ZONE, activity_end IN TIMESTAMP WITH TIME ZONE, type IN VARCHAR2, section IN VARCHAR2, level IN VARCHAR2) RETURN CLOB;

TBD

REPORT_LAST_ACTIVITY (new 23ai)

Undocumented dbms_swat.report_last_activity( type IN VARCHAR2, section IN VARCHAR2, level IN VARCHAR2) RETURN CLOB;

TBD