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 可以看下面的链接：

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-D1285CD5-95C0-4E74-8F26-A02018EA7999

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"，共同学习，共同成长！！！

当前文章：Oracle19C无法启用AutoIndexes特性
链接URL：http://kswsj.cn/article/jeeidd.html