Remove orphaned User-Schema-Geodatabase from SDE

522
1
Jump to solution
03-10-2022 05:14 AM
Stefan_Offermann
New Contributor III

Our customer has an Oracle database which reaches back to the year 2015. In 2018 and 2020, the schema of the tables changed, so each time new users were created, and the data was migrated to the new schemas. All geodatabases were created as User-Schema-Geodatabases. Last year, the DBA deleted the old users from 2015, but the SDE still contains references to the deleted users.

For example, the SDE.INSTANCES contains the rows with INSTANCE_NAME = RA2015, RB2015, RA2018, RB2018, RA2020, RB2020 and SDE.

When I now create a new Geodatabase RA2022 (no user-schema-gdb!), then the oracle user and the gdb schema can be created, and ArcCatalog can connect as new user. But it is impossible to view any table, or to create a table MyNewTable, which leads to an error:

Error executing stored procedure sde.instances_util.check_instance_table_conflicts::ORA-00942: Table or View not found

I suspect the SDE tries to look for MyNewTable in each INSTANCE_NAME, but as the users RA2015 and RB2015 have been deleted, the ORA-00942 "Table not found" gets thrown.

Is there a way to "sanitize" the SDE-internal tables from references to orphaned Oracle-Users/-Table/-whatever?

Is it recommended to just delete the orphans with DELETE FROM SDE.INSTANCES WHERE INSTANCE_NAME IN ('RA2015', 'RB2015'); ?

1 Solution

Accepted Solutions
Stefan_Offermann
New Contributor III

The deletion of the orphaned users from the INSTANCES table worked, no further problems occurred 🙂

DELETE FROM SDE.INSTANCES WHERE INSTANCE_NAME IN ('RA2015', 'RB2015');

View solution in original post

0 Kudos
1 Reply
Stefan_Offermann
New Contributor III

The deletion of the orphaned users from the INSTANCES table worked, no further problems occurred 🙂

DELETE FROM SDE.INSTANCES WHERE INSTANCE_NAME IN ('RA2015', 'RB2015');

0 Kudos