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
Subprograms
 
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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_SWAT_ARM_INTERNAL
DBMS_SWAT_INTERNAL
DBMS_SWAT_MM_INTERNAL
DBMS_SWAT_MM_UTILS
DBMS_SWAT_VER_INTERNAL
Materialized Views
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx