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

Last updated on MAY 01, 2023

Applies to:

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

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