Oracle Database

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

2 min read

Jun 8, 2020 12:00:00 AM

It’s one of those classic "Oracle knows best" moments, isn't it? You try to take back control of your own schema, and the database basically tells you, "I'm sorry, Dave, I'm afraid I can't do that."

Receiving an ORA-65532 when you're just trying to do some standard housekeeping is a bit of a shock, but it highlights just how protective the 19c Auto-Indexing background process is of its creations.

The Frustration of Dropping Auto-Indexes in 19c

Hello! I recently noticed that: What if I’m not happy with the Oracle algorithm and I want to drop an 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.

Identifying Auto-Created Indexes

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

The Rebuild-and-Drop Workaround

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

Controlling Auto-Indexing via DBMS_AUTO_INDEX

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 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.

Resource: Oracle DBMS_AUTO_INDEX Documentation

Pro Tip: Testing Performance Without Auto-Indexes

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:

“_optimizer_use_auto_indexes”=OFF

I hope this helps. Cheers!