ORA-01408: Such Column List Already Indexed on 12c Database (Doc ID 1944906.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 02-Sep-2016***

Symptoms

As per 12c documentation and new features "Creating Multiple Indexes on the Same Set of Columns" is possible when the indexes are different in some way see the following documentation links:
https://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN13845
https://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN13846
12C New Feature : Creating Multiple Indexes on the Same Set of Columns        [Doc ID 1600398.1]


followed the restriction however still getting ORA-01408: Such Column List Already Indexed"

SQL >create bitmap index idx_test11 on test11(name) invisible;
ERROR at line 1:
ORA-01408: such column list already indexed

Changes

 Test Case:

============

"compatible" parameter is set to 11.2.0.0.0   on 12c database

CREATE TABLE tab1
(DEPTNO1 NUMBER(2) CONSTRAINT PK_tab1 PRIMARY KEY,
DNAME1 VARCHAR2(14) ,
LOC1 VARCHAR2(13) ) ;

Table created.

SQL> CREATE TABLE tab2
(EMPNO1 NUMBER(4) CONSTRAINT PK_tab2 PRIMARY KEY,
ENAME1 VARCHAR2(10),
JOB1 VARCHAR2(9),
MGR1 NUMBER(4),
HIREDATE1 DATE,
SAL1 NUMBER(7,2),
COMM1 NUMBER(7,2),
DEPTNO1 NUMBER(2) CONSTRAINT FK_tab1 REFERENCES tab1); 

Table created.

SQL> create index IND1 on tab2 (EMPNO1, DEPTNO1);

Index created.


SQL> create bitmap index IND2 on tab2 (EMPNO1, DEPTNO1) invisible;
create bitmap index IND2 on tab2 (EMPNO1, DEPTNO1) invisible
                                 *
ERROR at line 1:
ORA-01408: such column list already indexed

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms