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
Analytic View Caching Support
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
General
ATU_IMPLEMENT
VARCHAR2(9)
'IMPLEMENT';
Data Types
TYPE level IS RECORD (
dim_name dbms_id,
hier_name dbms_id,
lvl_name dbms_id);
TYPE level_list IS TABLE OF level;
Dependencies
ALL_ANALYTIC_VIEWS
ALL_MVIEWS
DBMS_HIERARCHY
ALL_ANALYTIC_VIEW_BASE_MEAS
ALL_TABLES
DBMS_METADATA
ALL_ANALYTIC_VIEW_DIMENSIONS
ALL_TAB_COLS
DBMS_MVIEW
ALL_ANALYTIC_VIEW_HIERS
AVTUNE_ATU_MV_SEQ
DBMS_OUTPUT
ALL_ANALYTIC_VIEW_LEVELS
AVTUNE_AUTO_TUNE_LOG
DBMS_SCHEDULER
ALL_ANALYTIC_VIEW_LVLGRPS
AVTUNE_AV_CACHES
DBMS_STANDARD
ALL_ATTRIBUTE_DIMENSIONS
AVTUNE_AV_TUNING
DUAL
ALL_ATTRIBUTE_DIM_ATTRS
AVTUNE_DIM_CACHES
PLITBLM
ALL_ATTRIBUTE_DIM_KEYS
AVTUNE_LOG_LISTAGGCLOB
USER_SCHEDULER_JOBS
ALL_ATTRIBUTE_DIM_LEVELS
AVTUNE_QUERY_LOG_ARCHIVE
UTL_FILE
ALL_ATTRIBUTE_DIM_LEVEL_ATTRS
DBMS_ASSERT
V$DIAG_LOG_EXT
ALL_ATTRIBUTE_DIM_TABLES
DBMS_HCS_LIB
V$SQL
Documented
No
Exceptions
Error Code
Reason
ORA-18521
analytic view auto caching is not enabled.
First Available
20c
Role
SELECT 'sys_priv', privilege FROM dba_sys_privs WHERE grantee = 'AVTUNE_PKG_ROLE'
UNION
SELECT 'obj_priv', table_name OBJECT FROM dba_tab_privs WHERE grantee = 'AVTUNE_PKG_ROLE'
ORDER BY 1,2;
Create an aggregation cache with the specified level
dbms_avtune.auto_cache_create(
av_name IN dbms_id,
level_group IN sys.dbms_avtune.level_list,
av_owner IN dbms_id := sys_context('userenv','current_schema')
PRAGMA supplemental_log_data(auto_cache_create, UNSUPPORTED_WITH_COMMIT);
dbms_avtune.auto_cache_enable(
av_name IN dbms_id,
refresh_intvl IN INT := NULL,
num_queries IN INT := NULL,
avg_query_time IN NUMBER := NULL,
total_cache_pct IN INT := NULL,
init_max_pct IN INT := NULL,
init_numhier IN INT := NULL,
init_fixed_lvls IN level_list := NULL,
run_mode IN VARCHAR2 := ATU_IMPLEMENT,
av_owner IN dbms_id := sys_context('userenv','current_schema');
PRAGMA supplemental_log_data(auto_cache_enable, UNSUPPORTED_WITH_COMMIT);
dbms_avtune.auto_cache_modify(
av_name IN VARCHAR2,
refresh_intvl IN NUMBER,
num_queries IN NUMBER,
avg_query_time IN NUMBER,
total_cache_pct IN NUMBER,
av_owner IN VARCHAR2);
dbms_avtune.auto_cache_remove(
av_name IN dbms_id,
level_group IN sys.dbms_avtune.level_list,
av_owner IN dbms_id := sys_context('userenv','current_schema');
dbms_avtune.auto_cache_star_enable(
dim_name IN dbms_id,
av_name IN dbms_id := NULL,
refresh_intvl IN INT := NULL,
run_mode IN VARCHAR2 := atu_implement,
dim_owner IN dbms_id := sys_context('userenv','current_schema'),
av_owner IN dbms_id := sys_context('userenv','current_schema');
PRAGMA supplemental_log_data(auto_cache_star_enable, UNSUPPORTED_WITH_COMMIT);
Updates the query log archive and looks for/create cache tuples
dbms_avtune.auto_cache_tune(
av_name IN dbms_id,
num_queries IN INT := NULL,
avg_query_time IN NUMBER := NULL,
run_mode IN VARCHAR2 := atu_implement,
av_owner IN dbms_id := sys_context('userenv','current_schema');