创建用户，表空间
alter session set container=pdbprod5;Session altered.SQL> create tablespace test_data datafile '/u01/app/oracle/oradata/PRODCDB/PDBPROD5/test_data01.dbf' size 100m autoextend on;Tablespace created.SQL> create tablespace test_ind datafile '/u01/app/oracle/oradata/PRODCDB/PDBPROD5/test_ind01.dbf' size 200m autoextend on ;Tablespace created
SQL> create user test identified by test default tablespace test_data ;User created.
SQL> grant create session,unlimited tablespace to test;Grant succeeded.开启自动索引参数（AUTO_INDEX_MODE 为OFF表示未开启）
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A20
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;SQL> SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- --------------------6 AUTO_INDEX_SCHEMA6 AUTO_INDEX_DEFAULT_TABLESPACE6 AUTO_INDEX_SPACE_BUDGET 506 AUTO_INDEX_RETENTION_FOR_AUTO 3736 AUTO_INDEX_RETENTION_FOR_MANUAL6 AUTO_INDEX_MODE OFF6 AUTO_INDEX_COMPRESSION OFF6 AUTO_INDEX_REPORT_RETENTION 3738 rows selected.
--开启自动索引功能
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');PL/SQL procedure successfully completed.--指定test
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);PL/SQL procedure successfully completed--索引放在test_ind 表空间
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','test_ind');PL/SQL procedure successfully completed.--查询是否开启成功SQL> COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A20
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;SQL> SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- --------------------6 AUTO_INDEX_SCHEMA schema IN (TEST)6 AUTO_INDEX_DEFAULT_TABLESPACE TEST_IND6 AUTO_INDEX_SPACE_BUDGET 506 AUTO_INDEX_RETENTION_FOR_AUTO 3736 AUTO_INDEX_RETENTION_FOR_MANUAL6 AUTO_INDEX_MODE IMPLEMENT6 AUTO_INDEX_COMPRESSION OFF6 AUTO_INDEX_REPORT_RETENTION 3738 rows selected.创建测试表
SQL> CREATE TABLE test.test_tb AS SELECT rownum id, t.* FROM dba_objects t;Table created.
反复查询ID字段
DECLARE
a VARCHAR2(2000) := '';
BEGIN
FOR x IN 1..10000 LOOP
SELECT object_name INTO a FROM test.test_tb WHERE id = x;
END LOOP;
END;
/
查询是否创建成功
SQL> COLUMN OWNER FORMAT a10
col INDEX_TYPE format a10
col INDEX_NAME format a20
col TABLE_NAME format a20
col TABLE_OWNER format a10
SELECT OWNER,INDEX_TYPE,INDEX_NAME,TABLE_NAME,TABLE_OWNER FROM DBA_INDEXES WHERE AUTO='YES' ORDER BY OWNER,INDEX_NAME;SQL> SQL> SQL> SQL> SQL> OWNER INDEX_TYPE INDEX_NAME TABLE_NAME TABLE_OWNE
---------- ---------- -------------------- -------------------- ----------
TEST NORMAL SYS_AI_gydsz6587cb7q TEST_TB TEST只在 EXADATA systems 支持该特性，参考Doc ID 2570076.1
通过修改如下参数模拟测试，防止出现如下报错
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 9996
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 301
ORA-06512: at line 1
alter system set "_exadata_feature_on"=true scope=spfile;
