Enable and disable Automatic indexing in Oracle 19c

Enable and disable Automatic indexing in Oracle 19c

Automatic indexing is new feature used in Oracle 19c.
It will manage the create, drop and rebuild operation automatically based on application workload.
It will also help to generate reports for the indexes used to created or dropped.

DBMS_AUTO_INDEX package is used to manage the Oracle automatic indexing feature.

Check the Auto index is enabled or disabled

COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15

SELECT con_id, parameter_name, parameter_value
FROM cdb_auto_index_config where parameter_name = 'AUTO_INDEX_MODE'
ORDER BY 1;

Enable and disable the Auto index feature in database
AUTO_INDEX_MODE Parameter is used for define the value of it.
Values:
IMPLEMENT: In this mode new indexes are created as visible and available for use by the optimizer.
REPORT ONLY: In this mode new indexes are created as invisible mode, not available for use.
OFF: Turns off automatic indexing.

-- Enabled and implement
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

-- Disabled
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Enabled and Disabled at schema level

--Check for schema level
SELECT con_id, parameter_name, parameter_value
FROM cdb_auto_index_config where parameter_name = 'AUTO_INDEX_SCHEMA'
ORDER BY 1;

--Allow=>True : Add the specified schema to the inclusion list.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);

--Alow=>False: Add the specified schema to the exclusion list.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);

--ALLOW=>NULL: Remove the specified schema from the list to which it is currently added.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);

--Set to Default value, use AUTO_INDEX_MODE value for all Database.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

Check already created automatic index in Database

COLUMN owner FORMAT A30
COLUMN index_name FORMAT A30
COLUMN table_owner FORMAT A30
COLUMN table_name FORMAT A30

SELECT owner, index_name, index_type, table_owner, table_name
FROM dba_indexes
WHERE auto = 'YES'
ORDER BY owner, index_name;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.