title: Oracle SQL*Loader not honoring skip_index_maintenance parameter
body: <p>It appears as if Oracle SQL*Loader is not honoring <strong>skip_index_maintenance</strong> parameter.</p>
  
  <p>My control file:</p>
  
  <pre><code>unrecoverable
  load data
  infile 'filelist2.dat'
  append
  into table XML_TABLE
  xmltype(XMLDATA)
  (
   filename filler char(120),
   XMLDATA  lobfile(filename) terminated by eof
  )
  </code></pre>
  
  <p>Invoking SQL*Loader with:</p>
  
  <pre><code>sqlldr control=load_xml.ctl direct=true parallel=true skip_index_maintenance=true
  </code></pre>
  
  <p>Results in:</p>
  
  <pre><code>ORA-26002: Table &lt;MYUSERNAME&gt;."SYS_NTEPJTox0TQcLgU+XYHawPLg==" has index defined upon it.
  </code></pre>
  
  <p>Huh? I thought the indexes should have been been disabled/marked as unusable?  Could this be because the table in question was generated automatically when the XSD was registered with Oracle?</p>
  
  <p>Also if I remove the "parallel" argument:</p>
  
  <pre><code>sqlldr control=load_xml.ctl direct=true skip_index_maintenance=true
  </code></pre>
  
  <p>Then I get these errors:</p>
  
  <pre><code>OCI-21500: internal error code, arguments: [qmxdpStoreColl:1], [], [], [], [], [], [], []
  ÌïçUundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ6ØÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUErrors in file :
  OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
  OCI-21500: internal error code, arguments: [qmxdpStoreColl:1], [], [], [], [], [], [], []
  undefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ@6ØÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUErrors in file :
  OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
  OCI-21500: internal error code, arguments: [qmxdpStoreColl:1], [], [], [], [], [], [], []
  )9Ø)9Ø)9Ø)9Øundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextundefined symbol: _etextÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿp6ØÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbUÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿXÍbU
  </code></pre>
  
  <p>Surely that's not correct?</p>
  
  <p>Note this is a "clean" Oracle 11G database with one set of tables generated based upon an XML Schema.</p>
  
  <p>Conventional (non-direct), SQL*Loader loads work fine. All records are inserted</p>
  
  <p>EDIT: UPDATED WITH REQUESTED INFO</p>
  
  <p>First of all thanks for your help, its appreciated.</p>
  
  <p>I reverted to the Oracle examples to try and simplify. So XSD is:</p>
  
  <pre><code>&lt;xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
      xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0"
      xdb:storeVarrayAsTable="true"&gt;
      &lt;xs:element name="PurchaseOrder" type="PurchaseOrderType"
          xdb:defaultTable="PURCHASEORDER" /&gt;
      &lt;xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T"&gt;
          &lt;xs:sequence&gt;
              &lt;xs:element name="Reference" type="ReferenceType"
                  minOccurs="1" xdb:SQLName="REFERENCE" /&gt;
              &lt;xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS" /&gt;
              &lt;xs:element name="Reject" type="RejectionType" minOccurs="0"
                  xdb:SQLName="REJECTION" /&gt;
              &lt;xs:element name="Requestor" type="RequestorType"
                  xdb:SQLName="REQUESTOR" /&gt;
              &lt;xs:element name="User" type="UserType" minOccurs="1"
                  xdb:SQLName="USERID" /&gt;
              &lt;xs:element name="CostCenter" type="CostCenterType"
                  xdb:SQLName="COST_CENTER" /&gt;
              &lt;xs:element name="ShippingInstructions" type="ShippingInstructionsType"
                  xdb:SQLName="SHIPPING_INSTRUCTIONS" /&gt;
              &lt;xs:element name="SpecialInstructions" type="SpecialInstructionsType"
                  xdb:SQLName="SPECIAL_INSTRUCTIONS" /&gt;
              &lt;xs:element name="LineItems" type="LineItemsType"
                  xdb:SQLName="LINEITEMS" /&gt;
          &lt;/xs:sequence&gt;
      &lt;/xs:complexType&gt;
      &lt;xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"&gt;
          &lt;xs:sequence&gt;
              &lt;xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"
                  xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V" /&gt;
          &lt;/xs:sequence&gt;
      &lt;/xs:complexType&gt;
      &lt;xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T"&gt;
          &lt;xs:sequence&gt;
              &lt;xs:element name="Description" type="DescriptionType"
                  xdb:SQLName="DESCRIPTION" /&gt;
              &lt;xs:element name="Part" type="PartType" xdb:SQLName="PART" /&gt;
          &lt;/xs:sequence&gt;
          &lt;xs:attribute name="ItemNumber" type="xs:integer"
              xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER" /&gt;
      &lt;/xs:complexType&gt;
      &lt;xs:complexType name="PartType" xdb:SQLType="PART_T"&gt;
          &lt;xs:attribute name="Id" xdb:SQLName="PART_NUMBER"
              xdb:SQLType="VARCHAR2"&gt;
              &lt;xs:simpleType&gt;
                  &lt;xs:restriction base="xs:string"&gt;
                      &lt;xs:minLength value="10" /&gt;
                      &lt;xs:maxLength value="14" /&gt;
                  &lt;/xs:restriction&gt;
              &lt;/xs:simpleType&gt;
          &lt;/xs:attribute&gt;
          &lt;xs:attribute name="Quantity" type="moneyType"
              xdb:SQLName="QUANTITY" /&gt;
          &lt;xs:attribute name="UnitPrice" type="quantityType"
              xdb:SQLName="UNITPRICE" /&gt;
      &lt;/xs:complexType&gt;
      &lt;xs:simpleType name="ReferenceType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="18" /&gt;
              &lt;xs:maxLength value="30" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T"&gt;
          &lt;xs:sequence&gt;
              &lt;xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION"
                  xdb:SQLCollType="ACTION_V"&gt;
                  &lt;xs:complexType xdb:SQLType="ACTION_T"&gt;
                      &lt;xs:sequence&gt;
                          &lt;xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY" /&gt;
                          &lt;xs:element name="Date" type="DateType" minOccurs="0"
                              xdb:SQLName="DATE_ACTIONED" /&gt;
                      &lt;/xs:sequence&gt;
                  &lt;/xs:complexType&gt;
              &lt;/xs:element&gt;
          &lt;/xs:sequence&gt;
      &lt;/xs:complexType&gt;
      &lt;xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T"&gt;
          &lt;xs:all&gt;
              &lt;xs:element name="User" type="UserType" minOccurs="0"
                  xdb:SQLName="REJECTED_BY" /&gt;
              &lt;xs:element name="Date" type="DateType" minOccurs="0"
                  xdb:SQLName="DATE_REJECTED" /&gt;
              &lt;xs:element name="Comments" type="CommentsType" minOccurs="0"
                  xdb:SQLName="REASON_REJECTED" /&gt;
          &lt;/xs:all&gt;
      &lt;/xs:complexType&gt;
      &lt;xs:complexType name="ShippingInstructionsType"
          xdb:SQLType="SHIPPING_INSTRUCTIONS_T"&gt;
          &lt;xs:sequence&gt;
              &lt;xs:element name="name" type="NameType" minOccurs="0"
                  xdb:SQLName="SHIP_TO_NAME" /&gt;
              &lt;xs:element name="address" type="AddressType" minOccurs="0"
                  xdb:SQLName="SHIP_TO_ADDRESS" /&gt;
              &lt;xs:element name="telephone" type="TelephoneType"
                  minOccurs="0" xdb:SQLName="SHIP_TO_PHONE" /&gt;
          &lt;/xs:sequence&gt;
      &lt;/xs:complexType&gt;
      &lt;xs:simpleType name="moneyType"&gt;
          &lt;xs:restriction base="xs:decimal"&gt;
              &lt;xs:fractionDigits value="2" /&gt;
              &lt;xs:totalDigits value="12" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="quantityType"&gt;
          &lt;xs:restriction base="xs:decimal"&gt;
              &lt;xs:fractionDigits value="4" /&gt;
              &lt;xs:totalDigits value="8" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="UserType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="0" /&gt;
              &lt;xs:maxLength value="10" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="RequestorType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="0" /&gt;
              &lt;xs:maxLength value="128" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="CostCenterType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="1" /&gt;
              &lt;xs:maxLength value="4" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="VendorType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="0" /&gt;
              &lt;xs:maxLength value="20" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="PurchaseOrderNumberType"&gt;
          &lt;xs:restriction base="xs:integer" /&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="SpecialInstructionsType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="0" /&gt;
              &lt;xs:maxLength value="2048" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="NameType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="1" /&gt;
              &lt;xs:maxLength value="20" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="AddressType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="1" /&gt;
              &lt;xs:maxLength value="256" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="TelephoneType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="1" /&gt;
              &lt;xs:maxLength value="24" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="DateType"&gt;
          &lt;xs:restriction base="xs:date" /&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="CommentsType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="1" /&gt;
              &lt;xs:maxLength value="2048" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
      &lt;xs:simpleType name="DescriptionType"&gt;
          &lt;xs:restriction base="xs:string"&gt;
              &lt;xs:minLength value="1" /&gt;
              &lt;xs:maxLength value="256" /&gt;
          &lt;/xs:restriction&gt;
      &lt;/xs:simpleType&gt;
  &lt;/xs:schema&gt;
  </code></pre>
  
  <p>Registering schema and creating DB objects:</p>
  
  <pre><code>create or replace directory XSD as '/path/to/xsd'
  
  BEGIN
    DBMS_XMLSCHEMA.registerSchema(
      'http://www.oracle.com/PurchaseOrder.xsd',
      bfilename('XSD','PurchaseOrder.xsd'),
      TRUE,
      TRUE,
      FALSE,
      TRUE);
  END;
  </code></pre>
  
  <p>This creates the DB table and objects.</p>
  
  <p>When I try to SQL*Loader in direct mode (conventional works fine) with:</p>
  
  <pre><code>sqlldr control=load_po.ctl direct=true
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>ORA-26086: direct path does not support triggers
  </code></pre>
  
  <p>As I mentioned the Schema registration process appears to have create the following trigger:</p>
  
  <pre><code>create or replace trigger "SUKLTI"."PURCHASEORDER$xd" after delete or update on "SUKLTI"."PURCHASEORDER" for each row BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('SUKLTI','PURCHASEORDER', :old.sys_nc_oid$, '1100C2917B7B2ADEE053E5D81DAC36D4' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('SUKLTI','PURCHASEORDER', :old.sys_nc_oid$, '1100C2917B7B2ADEE053E5D81DAC36D4', user ); END IF; END;
  </code></pre>
  
  <p>I can't find any docs on "xdb_pitrig_pkg" to understand what its doing.</p>
  
  <p>Even if I drop the trigger I still get:</p>
  
  <pre><code>ORA-26086: direct path does not support triggers
  </code></pre>
  
  <p>When running the loader in direct mode</p>
  
  EDIT 16/03/2015
  
  <p>After much thought I've decided NOT to load the initial data into Schema backed tables. The main reason being that the performance was not very good.</p>
  
  <p>I have instead decided to perform my initial bulk XML load into XMLType tables that ARE NOT Schema backed. Even though not based upon a schema the XML will be checked for "well formedness". Loading this way is an order of magnitude faster as I am able to use SQL*Loader in direct mode with parallel e.g. </p>
  
  <pre><code>sqlldr control=control.ctl parallel=true direct=true
  </code></pre>
  
  <p>Now that I have my data loaded into these "staging" tables I'm using "<code>extract()</code>"</p>
  
  <p>and </p>
  
  <pre><code>sys_XMLGen
  </code></pre>
  
  <p>to extract only the required XML from the staging tables and copy into reporting tables. I might produce views to sit on top of the reporting tables for ease of use.</p>
  
  <p>Anyway guys, thanks for the help :)</p>