1.DROP TABLESPACE
In a more supported way, I can drop all AUTO indexes by dropping the tablespace where they reside. If I plan to do that, I’ve probably defined a specific tablespace for them (rather than the default tablespace for the user):
SQL> select parameter_name,parameter_value from dba_auto_index_config order by 1;
PARAMETER_NAME PARAMETER_VALUE
__________________________________ __________________
AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE AITBS
AUTO_INDEX_MODE IMPLEMENT
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50
This just works to remove all indexes created there:
SQL> drop tablespace AITBS including contents;
Tablespace dropped.
2.MOVE and DROP
I may not want to drop all of them. What if I move one index into a new tablespace? I don’t want to actually rebuild it, unusable is ok for me:
SQL> alter index ADMIN."SYS_AI_26rdw45ph3hag" rebuild tablespace EPHEMERAL unusable;
alter index ADMIN."SYS_AI_26rdw45ph3hag" rebuild tablespace EPHEMERAL unusable
*
ERROR at line 1:
ORA-14048: a partition maintenance operation may not be combined with other operations
Well, I don’t know how to do this without rebuilding it. So let’s do this:
SQL> create tablespace EPHEMERAL nologging;
Tablespace created.
SQL> alter user admin quota unlimited on EPHEMERAL;
User altered.
SQL> alter index ADMIN."SYS_AI_26rdw45ph3hag" rebuild tablespace EPHEMERAL online;
Index altered.
This works, so not all ALTER INEX commands fail with an ORA-65532.
SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';
OWNER INDEX_NAME OBJECT_ID AUTO TABLESPACE_NAME
_____ ____________________ _________ ____ _______________
ADMIN SYS_AI_gg1ctjpjv92d5 73192 YES AITBS
ADMIN SYS_AI_26rdw45ph3hag 73193 YES EPHEMERAL
And I can now drop this tablespace that contains only this index:
SQL> drop tablespace EPHEMERAL including contents;
Tablespace dropped.