Oracle 11g problem (ORA-00600 and ORA-30980) with XDB.XMLINDEX and XPath subsetting
Dear Forum,
I've run into a problem with an index of type XDB.XMLINDEX on Oracle 11g.
Initially, I successfully create a table with an XMLTYPE column using simple,
BASICFILE CLOB storage. I can also successfully create and then alter an
XDB.XMLINDEX with XPath subsetting. The problem arises when issuing the
Nth ALTER INDEX statement with the INCLUDE ADD clause to augment the XPath subsettings. After this Nth ALTER INDEX statement (typically fails after five or six ALTER INDEX statements), I receive either "ORA-00600: internal error" or "ORA-30980: Invalid Input".
The platform particulars are:
Oracle Version:
11.1.0.6.0, 64-bit (sqlplus reports SQL*Plus Release as "10.2.0.1.0", see below)
Operating System (Solaris: uname -a):
SunOS 5.10 Generic_118833-36 sun4v sparc SUNW,Sun-Fire-T200
Here is the output of the initially successful CREATE TABLE, CREATE INDEX
and ALTER INDEX statements, followed by the failed ALTER INDEX statement(s).
=========================================================
===== START SESSION TRANSCRIPT =============================
=========================================================
/u01/app/oracle/product/11.1.0/Db_1/bin/sqlplus scotty/tigger
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 3 18:46:56 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
CREATE TABLE BIZFORM_XML_DATA_X (
mc_db_seq NUMBER(16) NOT NULL,
is_db_seq NUMBER(16) NOT NULL,
xml_data XMLTYPE NOT NULL,
update_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
create_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
)
XMLTYPE COLUMN xml_data STORE AS CLOB (TABLESPACE PRJ_LOB CACHE)
STORAGE ( FREELISTS 10 ) TABLESPACE PRJ_DATA;
Table created.
SQL>
ALTER TABLE BIZFORM_XML_DATA_X
ADD ( CONSTRAINT PK_BIZFM_XML_DATA_X PRIMARY KEY (mc_db_seq)
USING INDEX STORAGE ( FREELISTS 10 ) TABLESPACE PRJ_IDX );
Table altered.
SQL>
CREATE INDEX BIZFORM_XMLIDX ON BIZFORM_XML_DATA_X (XML_DATA) INDEXTYPE IS XDB.XMLINDEX
PARAMETERS ('PATHS (INCLUDE ( /acme:AuxData
/acme:AuxData/acme:TransInfo
/acme:BizForm/acme:Corrections9901XFiling/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:Corrections9901ZFiling/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:Corrections9901Filing/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:CorrectionsMiscFiling/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:RetroCredits9901XFiling/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:RetroCredits9901ZFiling/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:RetroCredits9901Filing/acme:BizData/acme:SupportingDoc
)
NAMESPACE MAPPING ( xmlns="http://www.acme.com/bizfm" xmlns:acme="http://www.acme.com/bizfm" ))
PATH TABLE BIZFMPT_PATH_TABLE
PATH ID INDEX BIZFMPT_PATHID_IX
ASYNC (SYNC ALWAYS) STALE (FALSE)');
Index created.
SQL>
ALTER INDEX BIZFORM_XMLIDX1 REBUILD
PARAMETERS ('PATHS (INCLUDE ADD (
/acme:BizForm/acme:ParentFiling/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:BizData/acme:SupportingDoc
/acme:BizForm/acme:BizData/acme:ClaimsExplanationStatement
/acme:BizForm/acme:BizData/acme:ACM9903FilingF
/acme:BizForm/acme:BizData/acme:ACM9903FilingF/acme:PrepaidExpenses
/acme:BizForm/acme:BizData/acme:ACM9902
/acme:BizForm/acme:BizData/acme:ACM9902/acme:ACM9902FilingA
/acme:BizForm/acme:BizData/acme:ACM9902/acme:ACM9902FilingB
/acme:BizForm/acme:BizData/acme:ACM9902/acme:ACM9902FilingK
/acme:BizForm/acme:BizData/acme:ACM9902Z
/acme:BizForm/acme:BizData/acme:ACM9902Z/acme:ACM9902ZFilingA
)
NAMESPACE MAPPING ( xmlns="http://www.acme.com/bizfm" xmlns:acme="http://www.acme.com/bizfm" ))');
Index altered.
SQL>
ALTER INDEX BIZFORM_XMLIDX REBUILD
PARAMETERS ('PATHS (INCLUDE ADD (
/acme:BizForm/acme:BizData/acme:ACM9902/acme:ACM9902FilingK
/acme:BizForm/acme:BizData/acme:ACM9902X
/acme:BizForm/acme:BizData/acme:ACM9902X/acme:ACM9902XFilingA
/acme:BizForm/acme:BizData/acme:ACM9902X/acme:ACM9902XFilingB
/acme:BizForm/acme:BizData/acme:ACM9902X/acme:ACM9902XFilingK
/acme:BizForm/acme:BizData/acme:ACM9902X/acme:ACM9902XFilingK/acme:OverseasPayments
/acme:BizForm/acme:BizData/acme:ACM9902X/acme:ACM9902XFilingX
/acme:BizForm/acme:BizData/acme:ACM9902FilingYTD
/acme:BizForm/acme:BizData/acme:ACM9902FilingClaims
)
NAMESPACE MAPPING ( xmlns="http://www.acme.com/bizfm" xmlns:acme="http://www.acme.com/bizfm" ))');
Index altered.
SQL>
ALTER INDEX BIZFORM_XMLIDX REBUILD
PARAMETERS ('PATHS (INCLUDE ADD (
/acme:BizForm/acme:BizData/acme:ACM9902FilingClaims/acme:ShippingLossItems
/acme:BizForm/acme:BizData/acme:ACM1118
/acme:BizForm/acme:BizData/acme:ACM9901
/acme:BizForm/acme:BizData/acme:ACM9901/acme:ACM9901FilingA
/acme:BizForm/acme:BizData/acme:ACM9901/acme:ACM9901FilingC
/acme:BizForm/acme:BizData/acme:ACM9901/acme:ACM9901FilingJ
/acme:BizForm/acme:BizData/acme:ACM9901/acme:ACM9901FilingK
/acme:BizForm/acme:BizData/acme:ACM9901/acme:ACM9901FilingL
/acme:BizForm/acme:BizData/acme:ACM9901/acme:PaymentReconciliation
)
NAMESPACE MAPPING ( xmlns="http://www.acme.com/bizfm" xmlns:acme="http://www.acme.com/bizfm" ))');
Index altered.
SQL>
ALTER INDEX BIZFORM_XMLIDX REBUILD
PARAMETERS ('PATHS (INCLUDE ADD (
/acme:BizForm/acme:BizData/acme:ACM9901X
/acme:BizForm/acme:BizData/acme:ACM9901X/acme:ACM9901XFilingA
/acme:BizForm/acme:BizData/acme:ACM9901X/acme:ACM9901XFilingC
/acme:BizForm/acme:BizData/acme:ACM9901X/acme:ACM9901XFilingJ
/acme:BizForm/acme:BizData/acme:ACM9901X/acme:ACM9901XFilingL
/acme:BizForm/acme:BizData/acme:ACM9901X/acme:ACM9901XWaybill
/acme:BizForm/acme:BizData/acme:ACM9901X/acme:ACM9901XShipping
/acme:BizForm/acme:BizData/acme:ACM9901X/acme:ReimbTransactions
/acme:BizForm/acme:BizData/acme:ACM9901XFilingOverseasPayments
)
NAMESPACE MAPPING ( xmlns="http://www.acme.com/bizfm" xmlns:acme="http://www.acme.com/bizfm" ))');
Index altered.
SQL>
ALTER INDEX BIZFORM_XMLIDX REBUILD
PARAMETERS ('PATHS (INCLUDE ADD (
/acme:BizForm/acme:BizData/acme:ACM9901PL
/acme:BizForm/acme:BizData/acme:ACM9901Z
/acme:BizForm/acme:BizData/acme:ACM9901Z/acme:ACM9901XFilingA
/acme:BizForm/acme:BizData/acme:ACM9901Z/acme:ACM9901XFilingB
/acme:BizForm/acme:BizData/acme:ACM9901Z/acme:ACM9901XFilingK
/acme:BizForm/acme:BizData/acme:ACM9901ZFilingD
/acme:BizForm/acme:BizData/acme:ACM9901ZFilingYTD
/acme:BizForm/acme:BizData/acme:ACM9901ZFilingClaims
/acme:BizForm/acme:BizData/acme:ACM9901FilingD
/acme:BizForm/acme:BizData/acme:ACM9901FilingM
)
NAMESPACE MAPPING ( xmlns="http://www.acme.com/bizfm" xmlns:acme="http://www.acme.com/bizfm" ))');
Index altered.
SQL>
ALTER INDEX BIZFORM_XMLIDX REBUILD
PARAMETERS ('PATHS (INCLUDE ADD (
/acme:BizForm/acme:BizData/acme:ACM9901FilingClaims/acme:PrepaidItems
/acme:BizForm/acme:BizData/acme:ACM9901FilingClaims/acme:ShippingLossItems
/acme:BizForm/acme:BizData/acme:ACM9901FilingN
/acme:BizForm/acme:BizData/acme:ACM9901FilingO
/acme:BizForm/acme:BizData/acme:ACM9901FilingO/acme:ACM9901PartialReimb
/acme:BizForm/acme:BizData/acme:ACM9901FilingO/acme:ACM9901PartialPrepaid
/acme:BizForm/acme:BizData/acme:ACM9901FilingPH
/acme:BizForm/acme:BizData/acme:ACM9901Correction
/acme:BizForm/acme:BizData/acme:ACM9904
)
NAMESPACE MAPPING ( xmlns="http://www.acme.com/bizfm" xmlns:acme="http://www.acme.com/bizfm" ))');
ALTER INDEX BIZFORM_XMLIDX REBUILD
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1, ORA-00600: internal error code, arguments:
[qmixdCombineNS4], [], [], [], [], [], [], []
=========================================================
===== END SESSION TRANSCRIPT ===============================
=========================================================
As mentioned in the problem introduction, the "ALTER INDEX" statement sometimes fails with this error:
=========================================================
SQL> ALTER INDEX BIZFORM_XMLIDX REBUILD
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1, ORA-30980: Invalid Input.
=========================================================
Once "ORA-30980" has been received, the index cannot be dropped. For example:
=========================================================
SQL> purge recyclebin;
Recyclebin purged.
SQL> drop index BIZFORM_XMLIDX force;
drop index BIZFORM_XMLIDX force
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30967: operation directly on the Path Table is disallowed
=========================================================
Note that if only "ORA-00600" is received as an error as, the index can be dropped.
It seems likely that I am doing something wrong, or have missed a key setting, parameter or piece of information (is there a limit on the number of XPaths in the index's subset?). Our project would very much like to use Oracle's XMLTYPE, but without a functional XMLINDEX I don't think Oracle's native XML is an option. Your help is greatly appreciated.
Best regards,
S. Alexander
