Oracle SQL*Loader 不支持 skip_index_maintenance 参数

Question

似乎 Oracle SQL*Loader 不尊重 skip_index_maintenance 范围。

我的控制文件:

unrecoverable
load data
infile 'filelist2.dat'
append
into table XML_TABLE
xmltype(XMLDATA)
(
 filename filler char(120),
 XMLDATA  lobfile(filename) terminated by eof
)

使用以下命令调用 SQL*Loader:

sqlldr control=load_xml.ctl direct=true parallel=true skip_index_maintenance=true

结果是:

ORA-26002: Table <MYUSERNAME>."SYS_NTEPJTox0TQcLgU+XYHawPLg==" has index defined upon it.

嗯？我认为索引应该被禁用/标记为不可用？这可能是因为在向 Oracle 注册 XSD 时自动生成了有问题的表吗？

另外，如果我删除“并行”参数:

sqlldr control=load_xml.ctl direct=true skip_index_maintenance=true

然后我收到这些错误:

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

这肯定不正确吗？

请注意，这是一个“干净的”Oracle 11G 数据库，其中包含一组基于 XML 模式生成的表。

常规(非直接)SQL*Loader 加载工作正常。插入所有记录

编辑:更新请求的信息

首先感谢您的帮助，不胜感激。

我恢复到 Oracle 示例以尝试简化。所以 XSD 是:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0"
    xdb:storeVarrayAsTable="true">
    <xs:element name="PurchaseOrder" type="PurchaseOrderType"
        xdb:defaultTable="PURCHASEORDER" />
    <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
        <xs:sequence>
            <xs:element name="Reference" type="ReferenceType"
                minOccurs="1" xdb:SQLName="REFERENCE" />
            <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS" />
            <xs:element name="Reject" type="RejectionType" minOccurs="0"
                xdb:SQLName="REJECTION" />
            <xs:element name="Requestor" type="RequestorType"
                xdb:SQLName="REQUESTOR" />
            <xs:element name="User" type="UserType" minOccurs="1"
                xdb:SQLName="USERID" />
            <xs:element name="CostCenter" type="CostCenterType"
                xdb:SQLName="COST_CENTER" />
            <xs:element name="ShippingInstructions" type="ShippingInstructionsType"
                xdb:SQLName="SHIPPING_INSTRUCTIONS" />
            <xs:element name="SpecialInstructions" type="SpecialInstructionsType"
                xdb:SQLName="SPECIAL_INSTRUCTIONS" />
            <xs:element name="LineItems" type="LineItemsType"
                xdb:SQLName="LINEITEMS" />
        </xs:sequence>
    </xs:complexType>
    <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
        <xs:sequence>
            <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"
                xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V" />
        </xs:sequence>
    </xs:complexType>
    <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
        <xs:sequence>
            <xs:element name="Description" type="DescriptionType"
                xdb:SQLName="DESCRIPTION" />
            <xs:element name="Part" type="PartType" xdb:SQLName="PART" />
        </xs:sequence>
        <xs:attribute name="ItemNumber" type="xs:integer"
            xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER" />
    </xs:complexType>
    <xs:complexType name="PartType" xdb:SQLType="PART_T">
        <xs:attribute name="Id" xdb:SQLName="PART_NUMBER"
            xdb:SQLType="VARCHAR2">
            <xs:simpleType>
                <xs:restriction base="xs:string">
                    <xs:minLength value="10" />
                    <xs:maxLength value="14" />
                </xs:restriction>
            </xs:simpleType>
        </xs:attribute>
        <xs:attribute name="Quantity" type="moneyType"
            xdb:SQLName="QUANTITY" />
        <xs:attribute name="UnitPrice" type="quantityType"
            xdb:SQLName="UNITPRICE" />
    </xs:complexType>
    <xs:simpleType name="ReferenceType">
        <xs:restriction base="xs:string">
            <xs:minLength value="18" />
            <xs:maxLength value="30" />
        </xs:restriction>
    </xs:simpleType>
    <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T">
        <xs:sequence>
            <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION"
                xdb:SQLCollType="ACTION_V">
                <xs:complexType xdb:SQLType="ACTION_T">
                    <xs:sequence>
                        <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY" />
                        <xs:element name="Date" type="DateType" minOccurs="0"
                            xdb:SQLName="DATE_ACTIONED" />
                    </xs:sequence>
                </xs:complexType>
            </xs:element>
        </xs:sequence>
    </xs:complexType>
    <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T">
        <xs:all>
            <xs:element name="User" type="UserType" minOccurs="0"
                xdb:SQLName="REJECTED_BY" />
            <xs:element name="Date" type="DateType" minOccurs="0"
                xdb:SQLName="DATE_REJECTED" />
            <xs:element name="Comments" type="CommentsType" minOccurs="0"
                xdb:SQLName="REASON_REJECTED" />
        </xs:all>
    </xs:complexType>
    <xs:complexType name="ShippingInstructionsType"
        xdb:SQLType="SHIPPING_INSTRUCTIONS_T">
        <xs:sequence>
            <xs:element name="name" type="NameType" minOccurs="0"
                xdb:SQLName="SHIP_TO_NAME" />
            <xs:element name="address" type="AddressType" minOccurs="0"
                xdb:SQLName="SHIP_TO_ADDRESS" />
            <xs:element name="telephone" type="TelephoneType"
                minOccurs="0" xdb:SQLName="SHIP_TO_PHONE" />
        </xs:sequence>
    </xs:complexType>
    <xs:simpleType name="moneyType">
        <xs:restriction base="xs:decimal">
            <xs:fractionDigits value="2" />
            <xs:totalDigits value="12" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="quantityType">
        <xs:restriction base="xs:decimal">
            <xs:fractionDigits value="4" />
            <xs:totalDigits value="8" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="UserType">
        <xs:restriction base="xs:string">
            <xs:minLength value="0" />
            <xs:maxLength value="10" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="RequestorType">
        <xs:restriction base="xs:string">
            <xs:minLength value="0" />
            <xs:maxLength value="128" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="CostCenterType">
        <xs:restriction base="xs:string">
            <xs:minLength value="1" />
            <xs:maxLength value="4" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="VendorType">
        <xs:restriction base="xs:string">
            <xs:minLength value="0" />
            <xs:maxLength value="20" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="PurchaseOrderNumberType">
        <xs:restriction base="xs:integer" />
    </xs:simpleType>
    <xs:simpleType name="SpecialInstructionsType">
        <xs:restriction base="xs:string">
            <xs:minLength value="0" />
            <xs:maxLength value="2048" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="NameType">
        <xs:restriction base="xs:string">
            <xs:minLength value="1" />
            <xs:maxLength value="20" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="AddressType">
        <xs:restriction base="xs:string">
            <xs:minLength value="1" />
            <xs:maxLength value="256" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="TelephoneType">
        <xs:restriction base="xs:string">
            <xs:minLength value="1" />
            <xs:maxLength value="24" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="DateType">
        <xs:restriction base="xs:date" />
    </xs:simpleType>
    <xs:simpleType name="CommentsType">
        <xs:restriction base="xs:string">
            <xs:minLength value="1" />
            <xs:maxLength value="2048" />
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="DescriptionType">
        <xs:restriction base="xs:string">
            <xs:minLength value="1" />
            <xs:maxLength value="256" />
        </xs:restriction>
    </xs:simpleType>
</xs:schema>

注册模式和创建数据库对象:

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;

这将创建数据库表和对象。

当我尝试以直接模式(常规工作正常)使用 SQL*Loader 时:

sqlldr control=load_po.ctl direct=true

我得到:

ORA-26086: direct path does not support triggers

正如我提到的，架构注册过程似乎创建了以下触发器:

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;

我在“xdb_pitrig_pkg”上找不到任何文档来了解它在做什么。

即使我放下触发器，我仍然会得到:

ORA-26086: direct path does not support triggers

在直接模式下运行加载程序时

编辑 16/03/2015

经过深思熟虑，我决定不将初始数据加载到 Schema 支持的表中。主要原因是性能不是很好。

相反，我决定将初始批量 XML 加载到不支持架构的 XMLType 表中。即使不是基于模式，也会检查 XML 的“格式良好”。以这种方式加载速度快了一个数量级，因为我可以在直接模式下使用 SQL*Loader 和并行，例如

sqlldr control=control.ctl parallel=true direct=true

现在我已将数据加载到这些“暂存”表中，我正在使用“ extract()”

和

sys_XMLGen

仅从临时表中提取所需的 XML 并复制到报告表中。为了便于使用，我可能会生成位于报告表顶部的 View 。

无论如何，伙计们，感谢您的帮助:)

Answer 1

作为替代方法，您可以使用外部临时表而不是 SQL*Loader；就像是:

create table xml_stage (xmldata_clob clob)
organization external (
  type oracle_loader
  default directory XML_DIR
  access parameters (
    fields (filename char(120))
    column transforms (xmldata_clob from lobfile(filename))
  )
  location ('filelist2.dat')
);

insert into purchaseorder select xmltype(xmldata_clob) from xml_stage;

但这意味着将文件列表和服务器上的文件放在 Oracle 可见的目录中。