19c新特性自动索引在19.3删除的方法

刘泽宇

自动索引最初在19.3版本出现，同时在此功能的初始版本中，没有一种机制可以删除由自动索引功能创建的特定索引，或者首先阻止创建特定索引。
只有在19.5版本才能使用特定方法进行删除。
但在19.3版本中，真的无法删除自动索引吗？
我找到一篇帖子，介绍了一些可以让你做到的黑点子


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.