Error: Errors when creating or deleting Oracle user schema geodatabases
Partially populated user schema geodatabases and/or orphaned entries cause issues for creating/deleting user schema geodatabases. Until the partially created user schema geodatabases are cleaned up it may not be possible to create additional geodatabases within the same Oracle database.
Creating new user schema geodatabases using the 'Create Enterprise Geodatabase' tool may fail within the wizard and result in the following error within the sde_setup.log:
"[Tue Apr 09 07:29:30 2013] Error creating GDB_Tables_Last_Modified table...
[Tue Apr 09 07:29:30 2013] ERROR Creating Geodatabase tables
, Error = -511
,EXT_Error = -942
,EXT_ERROR1 = Error executing stored procedure sde.instances_util.check_instance_table_conflicts
,EXT_ERROR2 = ORA-00942: table or view does not exist".
When trying to delete an existing user schema geodatabase using sdesetup command line tools, the following error may be received if the user schema is not complete:
">sdesetup -o delete -d ORACLE11G -i esri_sde:<user_schema> -u <owner> -p <pwd>
Error deleting geodatabase, error = -416"
Connecting under 'Geodatabase Connection properties' to a partially created or orphaned user schema geodatabase returns the following error:
"Failed to connect to the specified server. The supplied Project Instanceinfo handle is bad"
• Deleting user schema geodatabase uses the sdesetup -o delete command up until version 10.2.2
• Starting at 10.3, a new Geoprocessing tool called 'Delete Schema Geodatabase' was created to delete user schema geodatabases.
• For the command line tool: The sdesetup is part of the ArcSDE admin command tools and can be installed as part of the ArcGIS Server/ArcGIS for Server > ArcSDE install media that is available via the Customer Care Portal
Partially created user schema geodatabases can be caused by dropping users within the database that own user schema geodatabases, leaving orphan entries within the SDE.Instances table.
Invalid database objects within the SDE user schema prior to using the 'Create Enterprise Geodatabase' tool can also cause issues when creating the new user schemas. ORA-29900, ORA-06540, and ORA-06553 errors can be associated with this related cause - more detail can be found in KB 37915.
It is also possible that additional attempts to create new user schema geodatabases fail, but create additional partial/corrupt user schema geodatabases.
Solution or Workaround
There are many causes for how user schema geodatabases become corrupt or partially created.
Please see the Deleting a geodatabase from a user's schema help topic for the correct steps to follow when deleting a user schema geodatabase.
- Narrowing down the issue can be done by examining the rows in the sde.instances table and comparing with schemas that exist in the Oracle database. To verify a partially created user schema geodatabase, the system tables can be reviewed in comparison to another user schema geodatabase.
- Please see the following article for information about error messages that may occur when invalid objects exist in a user's schema, and how to recompile invalid objects in the database for user's schemas:
KB 37915: Problem: Invalid objects and ST_GEOMETRY operators failure after revoking upgrade privileges.
- If it is determined that the issue is due to partially created or orphaned records in the sde.instances table, and help is needed troubleshooting the issue, please contact Esri Support Services.
- User-schema geodatabase management
- Delete Schema Geodatabase - 10.3
- Creating a geodatabase in a user
- Deleting a geodatabase from a user