how to name xmltype column index

Andy Steel

In Oracle 11g, the following command creates a table with a single XMLTYPE column.

CREATE TABLE EMPLOYEE
(
XMLTYPE details
);

Oracle will create an index called something like SYS_IL0000211081C00005$$ automatically for the column without me doing anything else. I presume this is because it's stored as a LOB behind the scenes.

Does anyone know how I can specify the name of the index when the column is created as opposed to leaving it to Oracle ? I need to control my index names for assisting schema comparisons.

Thanks in advance
Andy

605982

ALTER INDEX eSYS_IL0000211081C00005$$
RENAME TO <Newname>

Andy Steel

Thanks, but it's not really answering my problem. I would first have to identify the column using PL/SQL then perform the DDL to rename it.

I need to be able to specify the index name when creating the table column.

605982

[http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml] - DDL Commands for LOBs


Actually, one can not alter index on LOB column once created...

Above link provide info on creating LOB columns and related indexes, and (hopefully) solution for your question.

Andy Steel

Thanks, the following bit in bold shows it working.

CREATE TABLE CPNRULACTPRM
(
RULACTPRMIDD NUMBER, /* PK */
RULACTIDD1 NUMBER, /* Rule Action - FK:CPNRULACT->RULACTIDD */
PRMIDD1 NUMBER, /* Action Parameter Name - FK:CPNRULPRMDEF.PRMIDD */
RULACTPRMXML XMLTYPE, /* Action Parameter Value. XML conforming to FormattedExpression.xsd */
RULACTPRMORD NUMBER, /* Order (cosmetic only) */
CONSTRAINT IDX_PK_CPNRULACTPRM_PRMIDD
PRIMARY KEY (RULACTPRMIDD) USING INDEX TABLESPACE INDEXES,
CONSTRAINT CPNRULACTPRM_RULACTIDD1_NN
CHECK (RULACTIDD1 IS NOT NULL),
CONSTRAINT CPNRULACTPRM_PRMIDD1_NN
CHECK (PRMIDD1 IS NOT NULL),
CONSTRAINT CPNRULACTPRM_RULACTPRMORD_NN
CHECK (RULACTPRMORD IS NOT NULL),
CONSTRAINT CPNRULACTPRM_RULACTPRMXML_NN
CHECK (RULACTPRMXML IS NOT NULL)
) TABLESPACE CONFIG
XMLTYPE RULACTPRMXML
STORE AS CLOB (
TABLESPACE CONFIG
INDEX IDX_RULACTPRMXML_LOB (
TABLESPACE INDEXES
STORAGE (MAXEXTENTS UNLIMITED)
*)*
*)*;