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 is now availble. If you haven't you downloaded already: Why?

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