Technical Track Business Insights

19c Auto-Indexing: ORA-65532: Cannot Alter or Drop Automatically Created Indexes

2 min read

Jun 8, 2020

Hello! I recently noticed that: What if I’m not happy with the Oracle algorithm and I want to drop and auto-index on 19c? It should be easy, I initially thought. For example, something like “drop index XXXX;” right? Wrong. However, it's not that simple. See this example:

SQL> drop index PYTHIAN."SYS_AI_9xu652x5fyu5i";
 drop index PYTHIAN."SYS_AI_9xu652x5fyu5i"
  *
 ERROR at line 1:
 ORA-65532: cannot alter or drop automatically created indexes

That's a new error, as you can see. Let’s have a detailed look at my auto-created indexes. You can see them with the AUTO flag set to YES on the query below:

SQL> select owner, index_name, auto, tablespace_name from dba_indexes natural where auto='YES';
 OWNER INDEX_NAME AUTO TABLESPACE_NAME
 ________ _______________________ _______ __________________
 PYTHIAN SYS_AI_9xu652x5fyu5i YES PYTHIAN
 PYTHIAN SYS_AI_few32swe423dw YES PYTHIAN
 PYTHIAN SYS_AI_94osd824n202f YES PYTHIAN

So, I can't drop the auto-created indexes. Let's try altering it and set it as unusable then. This would achieve my intent and cause Oracle to not use the index anyway.

SQL> alter index PYTHIAN."SYS_AI_9xu652x5fyu5i" unusable;
 alter index PYTHIAN."SYS_AI_9xu652x5fyu5i" unusable
  *
 ERROR at line 1:
 ORA-65532: cannot alter or drop automatically created indexes

It appears I can't alter it either. However, there's something I can do:

SQL> alter index PYTHIAN."SYS_AI_9xu652x5fyu5i" rebuild tablespace PYTHIAN2 online;
 Index altered.

Not exactly what I wanted in the first place, but now I can use a workaround to move it to a new tablespace and then drop the tablespace:

SQL> drop tablespace PYTHIAN2 including contents;
 Tablespace dropped.

So the index is gone indeed:

SQL> select owner,index_name,auto,tablespace_name from dba_indexes natural where auto='YES';
 OWNER INDEX_NAME AUTO TABLESPACE_NAME
 ________ _______________________ _______ __________________
 PYTHIAN SYS_AI_few32swe423dw YES PYTHIAN
 PYTHIAN SYS_AI_94osd824n202f YES PYTHIAN

Okay, so that's a workaround. However, if you simply drop it, Oracle probably recreates it, based on the algorithm, correct? For this then, we have an easier method. Simply alter auto-indexing to report mode:

exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT');

That's is interesting, right? Oracle automates the creation but makes it difficult to manage once created. There are more options, though instead of changing all to report. You can exclude specific schemas from AUTO_INDEX, choose a default tablespace, define retention, and more. Here is the reference documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_INDEX.html Lastly, here's a quick tip for investigations: If you're wondering whether an auto-index is good (or bad) for your query, try running it after disabling auto-indexes (on a session-level) by using: