Oracle DBMS_AVTUNE

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

AUTO_CACHE_STAR_DDL_TRIGGER

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