English

Error: Underlying ORA-39083 and ORA-02270 errors encountered with Oracle Data Pump Import

Error Message

When using Oracle Data Pump to import a user's schema containing ArcSDE layers that are SDEBINARY (including SDELOB), the import can fail with the following error:

...
ALTER TABLE "WILSON"."PARCELS" ADD CONSTRAINT "A428_FK1" FOREIGN KEY ("SHAPE") REFERENCES "WILSON"."F428" ("FID") DISABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02270: no matching unique or primary key for this column-list
...

Cause

The cause of the problem is Oracle Bug 5523375, which is documented in Oracle Metalink document 455311.1: "Oracle Data Pump Export does not export disabled primary key (PK) constraints".

The disabled constraint is used by ArcSDE when a layer is moved between load only mode and normal io mode. During this operation of placing a layer back in normal io mode, the disabled constraint is enabled to ensure the correct primary key and foreign key relationship between the business table and feature table. If the constraint fails to initialize, an error is returned and the layer is not placed in normal io mode. If the constraint is successfully enabled, and the layer is placed into normal io mode, the constraint is then disabled. The constraint is not required when the layer is in normal io mode because it is the application (ArcSDE) that ensures the correct relation between the tables.

Solution or Workaround

Prior to performing the Oracle Data Pump export, drop any disabled foreign key constraints.

It is safe to remove the constraints prior to the export because the constraints are not required to be present when a layer is in normal io mode. The next time the layer is moved between load only and normal io mode and the constraint is not present, ArcSDE will create the constraint.

Before executing the Oracle Data Pump export, drop the constraints with the following SQL as the owner of the schema:

Code:
BEGIN
FOR drop_cur IN (SELECT constraint_name, table_name FROM user_constraints
WHERE status = 'DISABLED' AND constraint_name LIKE 'F%_PK'
OR constraint_name LIKE 'A%_FK1') LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||drop_cur.table_name||' DROP CONSTRAINT '||drop_cur.constraint_name||'';
END LOOP;
END;
/

Related Information