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;
