Oracle 19C 無法啟用Auto Indexes特性

環境：

OS:Red Hat Enterprise Linux Server release 7.5 (Maipo)

DB:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0

說明：

近期檢視Oracle 19C新特性，發現Auto Indexes特性特別實用，想看看效果;

1 檢視auto_index特性未啟用；

SQL> select * from dba_auto_index_config ;

2 在啟用Automatic Indexing 時報如下錯誤，不支援該特性：

SQL> alter session set container=cjcpdb;

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); END;

*

ERROR at line 1:

ORA-40216: feature not supported

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180

ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 283

ORA-06512: at line 1

問題原因：

只有在 EXADATA systems 系統下才支援 Auto Index 特性。

Auto indexing will be allowed only in EXADATA systems, if not it will raise ORA-40216: feature not supported when we try it turn it on in non exadata systems.

ORA-40216 When Using Auto Index Feature (Doc ID 2570076.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

SYMPTOMS

When attempting to use auto index feature ,e.g. DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
it reports

ORA-40216: feature not supported
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-6512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-6512: at "SYS.DBMS_AUTO_INDEX", line 283
ORA-6512: at line 1

CHANGES

CAUSE

This issue has been discussed in

Bug 30017206 : ADW : AUTO INDEX FEATURE NOT WORKING IN FUTURE PRODUCTION

Closed as 'Not a Bug.'

Development explains:
Auto indexing will be allowed only in EXADATA systems, if not it will raise ORA-40216: feature not supported when we try it turn it on in non exadata systems.

SOLUTION

Do not use AUTO INDEX FEATURE in non-exadata systems.

想學習 AUTO INDEX FEATURE 可以看下面的連結：

Database Administrator’s Guide --- 21 Managing Indexes

21.7 Managing Auto Indexes

You can use the automatic indexing feature to configure and use auto indexes in an Oracle database to improve database performance.

......

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle 19C 無法啟用Auto Indexes特性