topic Re: Cant Delete ArcSDE ORACLE in Geodatabase Questions

Cant Delete ArcSDE ORACLE

irishadar — Mon, 30 May 2016 09:11:03 GMT

hi,

We had full working Arcsde 10.4 and we needed to delete all the objects on the database , including arcsde's.

We didn't delete the users .

The goal was to import the fresh data from production.

In the process Arcsde became invalid and from now on we are not able to delete nor repair, so we deleted all object of the user sachems and then tried to delete from the SDE schema but left with 2 objects that cannot be deleted :GDB_ITEMS,ST_DOMAIN STATS.

this is the error we are getting :

[‎30/‎05/‎2016 12:02] Evgenia Yertsenkin:

SQL> drop table SDE.GDB_ITEMS cascade constraints;

drop table SDE.GDB_ITEMS cascade constraints

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-21700: object does not exist or is marked for delete

SQL> drop type SDE.ST_DOMAIN_STATS force;

drop type SDE.ST_DOMAIN_STATS force

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-21700: object does not exist or is marked for delete

What should we do, do we need to drop and recreate the whole database including ArcSDE or is there a way that we can drop and create only ArcSDE .

it is a very critical issue for us and prompt answer will be much appreciated.

iris hadar

IAA

Re: Cant Delete ArcSDE ORACLE

Anonymous User — Mon, 30 May 2016 09:39:33 GMT

It seems to be a problem similar to this one:

Ora-21700 Object Does Not Exist Or Is Marked For Delete&nbsp; With ST_GEOMETRY

Try

DROP USER SDE CASCADE;

It this work, you can recreate again the SDE user/schema. If not:

"Either restore the Oracle instance from backup (restore both the SDE user and the schema with st_geometry attributes) or drop the impacted users from the Oracle instance." at http://support.esri.com/technical-article/000009908

Regards

Jesús de Diego

Re: Cant Delete ArcSDE ORACLE

irishadar — Mon, 30 May 2016 11:13:52 GMT

thank you for the prompt answer ,

we tried , it didn't work ,

this is the error msg:

SQL> DROP USER SDE CASCADE;

DROP USER SDE CASCADE

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-21700: object does not exist or is marked for delete

any idea what to do ?

Re: Cant Delete ArcSDE ORACLE

VinceAngelo — Mon, 30 May 2016 18:26:08 GMT

Unfortunately, you've corrupted the Oracle instance beyond its ability to recover. The only ways to clear this issue are:

Restore from backup, then delete all user-owned tables referencing ST_GEOMETRY or ST_RASTER objects, then drop all of SDE, or
Create a new instance and reload.

Since your goal was to repopulate, the second option will likely be easier.

- V

Re: Cant Delete ArcSDE ORACLE

irishadar — Tue, 31 May 2016 06:25:20 GMT

Hi,

Thank you for the quick response.

Our local distributor suggested that we will try to use the "Create Enterprise Geodatabase" Tool. We did so and it allowed us to delete the SDE user.

From that point we were able to create SDE user again and import the data correctly from production.

Could you please advise us what is the correct procedure for reloading data from production, given that we don't want to delete the entire database?

These are the steps we believe should be done:

1. delete all user-owned objects from all schemas except SDE (using sql drop command).

2. we have two ways here to remove SDE schema :

a. using the "Delete Schema Geodatabase" tool.

b. using sql drop command - should we delete first the tables referencing ST_GEOMETRY or ST_RASTER and then delete those types or the order doesn't matter?

We would be happy to receive your opinion of all of the above.

Iris and Evgenia

IAA

Re: Cant Delete ArcSDE ORACLE

AlexanderBrown5 — Tue, 28 Jun 2016 17:41:20 GMT

Iris,

If I understand correctly, your normal workflow is to just reload all of your production data into this database? Do you have an Oracle DBA on staff? Can you utilize Oracle Database replication?

One option, if possible, delete this newer database and have your DBA clone production to have the same Database name, tns information, as your deleted database (destination of production data).

Another option is to write a python script that will loop through all datasets, feature classes, etc and truncate and append the new features (will work as long as there are no schema changes).

Can you elaborate on your desired procedure, database items (number of items, sizes), and anything else that can be helpful?

Regards,

Alex

Re: Cant Delete ArcSDE ORACLE

BirajaNayak — Fri, 08 Jul 2016 17:33:03 GMT

You can take a look at the following procedure which generally we follow on testing environment:

1. Drop all user except sde with cascade option

2. Drop sde user with cascade option

3. Recreate all users including sde

4. Grant required permission

5. Reload the data by imp or impdp - import sde schema

6. Import remaining schema.

Hope this helps.

- Biraja

Re: Cant Delete ArcSDE ORACLE

irishadar — Tue, 20 Sep 2016 10:01:37 GMT

Thank you all for the suggestions,

It is not working with the “Drop cascade option”, it has dependencies in “table_registry”, and we had to move the data from one machine to another.

What we ended up doing was:

Disable all FK
Delete all layers that has entry in “table_registry” using python or arc Catalog.
Drop all objects from all the users
Drop SDE user cascade
Import all data from the other machine.
Rebuild FK

Best,

Iris hadar