Oracle DBMS_AVTUNE_UTIL
Version 21c

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 Utility functions for Analytic View auto cache auto tune.
AUTHID CURRENT_USER
Dependencies
ALL_OBJECTS DBMS_HCS_LOG ORA_DICT_OBJ_NAME
AVTUNE_AV_CACHES$ DBMS_STANDARD ORA_DICT_OBJ_OWNER
AVTUNE_AV_TUNING$ DICTIONARY_OBJ_NAME ORA_DICT_OBJ_TYPE
AVTUNE_DIM_CACHES$ DICTIONARY_OBJ_OWNER ORA_SYSEVENT
DBMS_AVTUNE    
Documented No
First Available 21c
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;

SYS_PRI   PRIVILEGE
--------  --------------------------
obj_priv  AVTUNE_AV_CACHES$
obj_priv  AVTUNE_AV_TUNING$
obj_priv  AVTUNE_DIM_CACHES$
obj_priv  AVTUNE_LOG_LISTAGGCLOB$
obj_priv  AVTUNE_QUERY_LOG_ARCHIVE$
obj_priv  DBMS_HCS_LOG
obj_priv  V_$DIAG_LOG_EXT
obj_priv  V_$SQL
sys_priv  ALTER SESSION
sys_priv  CREATE JOB
sys_priv  CREATE MATERIALIZED VIEW
sys_priv  CREATE TABLE
sys_priv  CREATE TRIGGER
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsavtune.sql
Subprograms
 
AUTO_CACHE_DDL_TRIGGER (new 21c)
Updates avtune auto cache system tables

The relationship to a trigger is not obvious
dbms_avtune_util.auto_cache_ddl_trigger(
p_stmt   IN VARCHAR2,
p_obj_id IN NUMBER);
conn / as sysdba

GRANT execute ON dbms_avtune_util TO c##uwclass;

conn c##uwclass

CREATE TABLE t (
testcol VARCHAR2(20));

SELECT object_id
FROM user_objects
WHERE object_name = 'T';

 OBJECT_ID
----------
     77238


DECLARE
 str dbms_id := 'CREATE TRIGGER x AFTER UPDATE ON t BEGIN NULL; END testtrig;';
BEGIN
  execute immediate str;
END;
/

DROP TRIGGER x;

DECLARE
 str dbms_id := 'CREATE TRIGGER x AFTER UPDATE ON t BEGIN NULL; END testtrig;';
 obj_id         INTEGER := 77238;
BEGIN
  sys.dbms_avtune_util.auto_cache_ddl_trigger(str, obj_id);
END;
/

PL/SQL procedure successfully completed.

SELECT owner, object_type
FROM dba_objects
WHERE object_name = 'X';

no rows selected

-- the trigger was not created so p_stmt is likely not DDL to be executed
-- apparently p_stmt is a statement that a trigger is supposed to execute
-- but we are unable to find any dependent triggers: more work will be required
 
AUTO_CACHE_STAR_DDL_TRIGGER (new 21c)
Updates avtune auto cache system tables

The relationship to a trigger is not obvious
dbms_avtune_util.auto_cache_star_ddl_trigger(
p_stmt   IN VARCHAR2,
p_obj_id IN NUMBER);
TBD

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_AVTUNE
DBMS_HCS_LOG
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx