My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.