Oracle | DBMS_AVTUNE

Oracle DBMS_AVTUNE_UTIL Version 23c

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 Utility functions for Analytic View auto cache auto tune.

AUTHID CURRENT_USER

Dependencies

ALL_OBJECTS	DBMS_ASSERT	DUAL
AVTUNE_AV_CACHES$	DBMS_AVTUNE	ORA_DICT_OBJ_NAME
AVTUNE_AV_CACHE_LVLS$	DBMS_HCS_LOG	ORA_DICT_OBJ_OWNER
AVTUNE_AV_TUNING$	DBMS_STANDARD	ORA_DICT_OBJ_TYPE
AVTUNE_DIM_CACHES$	DICTIONARY_OBJ_NAME	ORA_SYSEVENT
AVTUNE_TEST_PARAMS$	DICTIONARY_OBJ_OWNER	PLITBLM

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
{ORACLE_HOME}/rdbms/admin/prvtavtune.plb

Subprograms

AUTO_ASSERT	AUTO_CLEAR_CLOB	AUTO_LOG_CLEAR_CLOB
AUTO_BUILD_CLOB	AUTO_CREATE_MV	AUTO_LOG_END
AUTO_CACHE_DDL_TRIGGER	AUTO_GET_TEST_PARAM_VALUE	AUTO_LOG_START
AUTO_CACHE_STAR_DDL_TRIGGER	AUTO_LOG	AUTO_SET_TEST_PARAM

AUTO_ASSERT (new 23c)

Undocumented dbms_avtune_util.auto_assert(

TBD

AUTO_BUILD_CLOB (new 23c)

Undocumented dbms_avtune_util.auto_build_clob(

TBD

AUTO_CACHE_DDL_TRIGGER

Updates the 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

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