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