English

Error: Oracle error ORA-04020 when importing SDE schema with st_geometry types

Error Message

When using Oracle's import utility or datapump and importing the ArcSDE SDE schema, the following error can occur:

Code:
IMP-00003: ORACLE error 4020 encountered
ORA-04020: deadlock detected while trying to lock object 30x70000004932FE480x70000003C7213080x70000004ADB1C00
IMP-00017: following statement failed with ORACLE error 4020:
� "ASSOCIATE STATISTICS WITH TYPES "ST_GEOMETRY" USING
"ST_DOMAIN_STATS""

Cause

The cause of the problem is Oracle Bug 4454890 (see Oracle Metalink for additional information).

When the error is encountered during the import process, the associations, which are required between the st_geometry, st_spatial_index, and relational operators with the st_domain_stats type, are not established. Without the associations, the optimizer does not gather statistics for st_geometry attributes, spatial indexes, and does not call the st_domain_stats type when parsing a SQL statement with st_geometry relational operators for calculating selectivity and cost.

Solution or Workaround

After the import process fails, connect in SQL*Plus as the SDE user, and execute the following commands to set the association:

Code:
SQL> ASSOCIATE STATISTICS WITH TYPES
2 sde.st_geometry USING sde.st_domain_stats;

SQL> ASSOCIATE STATISTICS WITH INDEXTYPES
2 sde.st_spatial_index USING sde.st_domain_stats;

SQL> ASSOCIATE STATISTICS WITH PACKAGES
2 sde.st_domain_operators, sde.st_relation_operators
3 USING sde.st_domain_stats;