ORA-01408: Such Column List Already Indexed" When Adding New XMLTYPE Column (Doc ID 1902606.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms

Error ORA-01408: such column list already indexed" when adding new XMLTYPE column appears when performing the steps below

1. Create table with two schema-based XMLTYPE columns (doc1, doc2) stored as OBJECT RELATIONAL.
2. Delete column (doc1).
3. Register new schema.
4. Add new XMLTYPE column (doc3) stored as OBJECT RELATIONAL.

Result is error "ORA-01408: such column list already indexed"



Example:

SQL> create table test_multi_xml (
  2 doc1 xmltype
  3 , doc2 xmltype
  4 )
  5 xmltype column doc1 store as object relational xmlschema "workbook.xsd" element "workbook"
  6 xmltype column doc2 store as object relational xmlschema "workbook.xsd" element "workbook"
  7 ;

Table created.


SQL> alter table test_multi_xml drop column doc1;

Table altered.


SQL> alter table test_multi_xml add (doc3 xmltype)
  2 xmltype column doc3 store as object relational
  3 xmlschema "workbook.xsd" element "workbook"
  4 ;

alter table test_multi_xml add (doc3 xmltype)
*
ERROR at line 1:
ORA-01408: such column list already indexed



Workaround:
When XMLTYPE column is stored as BINARY XML, then it works correctly.
It also works if we first add the new column, then drop the old one, instead of dropping it first.

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