General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
Purpose
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