Unable to create or install the Esri spatial type for Oracle (-521)

Description

When attempting to create or install the Esri ST_Geometry spatial type, ERROR setting up ST_Geometry, Error = -521 occurs with the following error messages.

Code:
[5/12/2011 5:12:44 PM] Error: Could not create geodatabase tables and stored procedures. [Failed to setup ST_Geometry type. (Unable to load Type ST_Geometry, underlying DBMS error encountered.)]
[5/12/2011 5:12:44 PM] Error: See the setup log in the following location for additional error information.
[5/12/2011 5:12:44 PM] Error: C:\Users\clint\AppData\Local\Temp\1\sde_setup.log

Code:
[Thu May 12 17:12:38 2011] ERROR setting up ST_Geometry type.
[Thu May 12 17:12:38 2011] ERROR setting up St_Geometry, Error = -521

Cause

In Oracle, if a table exists that has a column type of PUBLIC.ST_GEOMETRY, the creation of the Esri SDE.ST_GEOMETRY type fails.

Solution or Workaround

1. Find the tables that reference a PUBLIC.ST_GEOMETRY column.
   
   

   Code:
   sqlplus sys/manager as sysdba
   
   set pages 9999
   
   SELECT OWNER, NAME, TYPE FROM DBA_DEPENDENCIES WHERE REFERENCED_OWNER='MDSYS' AND REFERENCED_NA
   ME  = 'ST_GEOMETRY' AND REFERENCED_TYPE  = 'TYPE' AND TYPE='TABLE';
   
   OWNER                       NAME                           TYPE
   --------------------------- ------------------------------ ------------------
   MAP                         TEST                           TABLE
   

   
   
2. Drop the table found in Step 1.
   
   

   Note:
   If the table is needed for any reason, backup the table before dropping it. This table can be restored after Esri ST_Geometry is installed.

   
   
   

   Code:
   drop table map.test;
   

   
   
3. Purge the recycle bin.
   
   

   Code:
   purge dba_recyclebin;

   
   
4. Re-run the install of the Esri ST_Geometry type.