Oracle | DBMS_AUTO

Oracle DBMS_AUTO_INDEX Version 21c

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 The Oracle Database periodically runs a task, SYS_AUTO_INDEX_TASK that monitors the system and creates indexes based on workload running in the system. This package contains subprograms that display the report of the task, configure task execution and behavior.

AUTHID CURRENT_USER

Dependencies

ALL_CONSTRAINTS	ALL_USERS	DBMS_SPM_INTERNAL
ALL_CONS_COLUMNS	DBMS_AUTO_INDEX_INTERNAL	DBMS_STANDARD
ALL_INDEXES	DBMS_AUTO_INDEX_LIB	DBMS_STATS_INTERNAL
ALL_IND_COLUMNS	DBMS_REPORT	PRVT_REPORT_TAGS
ALL_TABLES	DBMS_SMB	XMLTYPE

Documented Yes: Packages and Types Reference

First Available 19c

Security Model Owned by SYS with EXECUTE granted to the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmsai.sql

Subprograms

CONFIGURE	DROP_SECONDARY_INDEXES	REPORT_LAST_ACTIVITY
DROP_AUTO_INDEXES	REPORT_ACTIVITY	Related Queries

CONFIGURE

Sets configuration options for auto indexing. dbms_auto_index.configure( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, allow IN BOOLEAN := TRUE);

exec dbms_auto_index.configure('AUTO_INDEX_REPORT_RETENTION', '90', TRUE);

DROP_AUTO_INDEXES (new 21c)

Allows dropping automatically created index dbms_auto_index.drop_auto_indexes( owner IN VARCHAR2, index_name IN VARCHAR2, allow_recreate IN BOOLEAN);

exec dbms_auto_index.drop_auto_indexes('C##UWCLASS', '', TRUE);

      

      PL/SQL procedure successfully completed.

DROP_SECONDARY_INDEXES

Drops indexes that are not used for constraints for given (optional) schema and table. If no schema is given, all indexes which the user has priviledges on will be dropped. If no table is given, all indexes within the given schema will be dropped. dbms_auto_index.drop_secondary_indexes( ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL);

exec dbms_auto_index.drop_secondary_indexes('UWCLASS', 'SERVERS');

REPORT_ACTIVITY

Reports the results of auto index executions between specified activity_start and activity_end dbms_auto_index.report_activity( activity_start IN TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP - 1, activity_end IN TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP, type IN VARCHAR2 := 'TEXT', section IN VARCHAR2 := 'ALL', level IN VARCHAR2 := 'TYPICAL') RETURN CLOB);

exec dbms_auto_index.report_activity(SYSTIMESTAMP-24, SYSTIMESTAMP, 'TEXT', 'INDEX_DETAILS', 'ALL');

REPORT_LAST_ACTIVITY

Reports the results of last auto index task execution dbms_auto_index.report_last_activity( type IN VARCHAR2 := 'TEXT', section IN VARCHAR2 := 'ALL', level IN VARCHAR2 := 'TYPICAL') RETURN CLOB;

spool /home/oracle/autoidx_last_activity.html

      

      exec dbms_auto_index.report_last_activity('HTML', 'ALL', 'ALL');

      

      spool off

Related Queries

Query to return auto-indexes SELECT owner, index_name, index_type, table_owner, table_name, table_type FROM dba_indexes WHERE auto = 'YES' ORDER BY 1,2;