English

Error: Underlying "ORA-30512: cannot modify SDE.ST_GEOMETRY_COLUMNS more than once in a transaction" encountered with Oracle Import Utility

Error Message

While using the Oracle IMP Utility to import an ArcSDE sde schema, ORA-30512 is displayed at the end of the Oracle import log.
...
IMP-00017: following statement failed with ORACLE error 30512:
"ALTER TABLE "ST_GEOMETRY_COLUMNS" ENABLE CONSTRAINT "GEOM_SRID_FK1""
IMP-00003: ORACLE error 30512 encountered
ORA-30512: cannot modify SDE.ST_GEOMETRY_COLUMNS more than once in a transaction
Import terminated successfully with warnings.

Cause

The Oracle error being encountered is a limitation of the Oracle IMP utility. After all schema tables are imported, the import process attempts to enable each table's constraints. The st_geometry_columns table contains a foreign key constraint named GEOM_SRID_FK1, which ensures all values for the SRID attribute reference valid SRID values in the st_spatial_references table. When the constraint is altered by the import utility, the ArcSDE trigger DB_EV_ALTER_ST_METADATA is then executed because of the DDL operation and encounters the Oracle error ORA-30512.

Solution or Workaround

Oracle recommends using its new utility Oracle Datapump when performing an export and import. If one were to use Oracle's Datapump, this Oracle error would not be encountered.

It is safe to ignore the Oracle IMP utility error ORA-30512.

Even though the error is encountered and reported in the Oracle import log, the GEOM_SRID_FK1 constraint is successfully enabled.

Note:
Execute the following SQL statement as the SDE user to confirm the GEOM_SRID_FK1 constraint is enabled after the import.

Code:
SQL> SELECT constraint_name, status FROM user_constraints WHERE constraint_name = 'GEOM_SRID_FK1';

CONSTRAINT_NAME STATUS
------------------------------ --------
GEOM_SRID_FK1 ENABLED

    Related Information