topic Re: Some ghost tables in ArcSDE (Oracle) in Data Management Questions

Some ghost tables in ArcSDE (Oracle)

StéphaneHenriod — Thu, 07 Nov 2013 13:02:12 GMT

Hello

I have a geodatabase in Oracle, with many tables in many schemas. My problem is that some tables (and even some schemas) have been deleted in Oracle, but still appear in ArcGIS. Obviously, no data can be displayed
[ATTACH=CONFIG]28926[/ATTACH]

Deleting those tables is not possible:
[ATTACH=CONFIG]28927[/ATTACH]

I assume that there must be some cached info somewhere (in an Oracle table?). Has anyone been facing such a problem and do you have any idea how I can solve it?

Thanks in advance

Stéphane

PS: ArcGIS 10.1 & Oracle 11.2

Re: Some ghost tables in ArcSDE (Oracle)

Anonymous User — Thu, 07 Nov 2013 19:26:52 GMT

Original User: mboeringa2010

I think your first stop is your DBA to ask for a restore from a backup, prior to the deletions. That the data still appears in ArcGIS, is probably due to the data being registered in the ArcSDE Repository (e.g. see here for more info), as part of your geodatabase.

If a backup is not an option, you should probably contact ESRI to help restore the repository or discuss other options.

Re: Some ghost tables in ArcSDE (Oracle)

StéphaneHenriod — Thu, 07 Nov 2013 19:50:49 GMT

Hi

restore is unfortunately not an option. Some tables have been deleted 2 years ago and we obviously can't restore to a point that far in the past...

I will have a look at your link and contact ESRI if this proves insufficient.

Cheers

Stéphane

Re: Some ghost tables in ArcSDE (Oracle)

Anonymous User — Thu, 07 Nov 2013 20:05:18 GMT

Original User: vangelo

It is not generally necessary to use backups if you simply want to remove the corruption
from your geodatabase metadata. It is generally enough to create a new table with the
name of the missing table ("CREATE TABLE foo (objectid INTEGER NOT NULL)"), then
use Desktop to remove it again.

If you have complex relationships involving the corrupt table, Desktop may be less willing
to delete it, at which point it would be wise to get Tech Support on the line.

- V

Re: Some ghost tables in ArcSDE (Oracle)

EmadAl-Mousa — Sun, 10 Nov 2013 03:33:31 GMT

as vince mentioned you can re-create the table from Oracle Side using SQL Command:

create table MEYER.G2B09_POLYGON ( objectid INTEGER NOT NULL);

then, re-connect to your schema "MEYER" in ArcCatalog and delete it.

Or you can use the sdelayer -o delete command to remove the layer. this will clean sde metadata tables.

Re: Some ghost tables in ArcSDE (Oracle)

Anonymous User — Sun, 10 Nov 2013 11:48:11 GMT

Original User: vangelo

No, do not use 'sdelayer -o delete' for this purpose. 'sdelayer -o delete' should
almost never be used:

It does not delete all SDE metadata associated with the table (as 'sdetable -o delete'

It does not delete geodatabase metadata associated with the table ('sdetable -o delete'

You should always use ArcGIS to delete tables which have been manipulated by
ArcGIS or otherwise registered with the geodatabase.

- V

Re: Some ghost tables in ArcSDE (Oracle)

StéphaneHenriod — Mon, 11 Nov 2013 06:45:18 GMT

Thanks everybody for the answers

Isn't there any other option than recreating the tables in Oracle and then deleting them in Desktop? There are quite a lot of them, including in schemas that no longer exist and this would require quite some manual work.

I would have hoped that there is some kind of metadata table that can simply be purged?

Re: Some ghost tables in ArcSDE (Oracle)

StéphaneHenriod — Mon, 11 Nov 2013 06:46:13 GMT

Thanks everybody for the answers

Isn't there any other option than recreating the tables in Oracle and then deleting them in Desktop? There are quite a lot of them, including in schemas that no longer exist and this would require quite some manual work. There is no complex relationship though.

I would have hoped that there is some kind of metadata table that can simply be purged?

Re: Some ghost tables in ArcSDE (Oracle)

Anonymous User — Mon, 11 Nov 2013 07:44:29 GMT

Original User: mboeringa2010

Thanks everybody for the answers

Isn't there any other option than recreating the tables in Oracle and then deleting them in Desktop? There are quite a lot of them, including in schemas that no longer exist and this would require quite some manual work. There is no complex relationship though.

I would have hoped that there is some kind of metadata table that can simply be purged?

No, you can not "purge a metadata table", they are vital elements of the geodatabase. It is the records in these geodatabase and ArcSDE system tables referring to your deleted datasets / tables, that need to be "purged" / deleted. By recreating the schema's and tables, ArcGIS will be able to recognize them again, and delete the proper records in the geodatabase and ArcSDE system tables, if I understand the suggestions by Vince and Emad Al-Mousa well.

Re: Some ghost tables in ArcSDE (Oracle)

StéphaneHenriod — Mon, 11 Nov 2013 08:07:20 GMT

Thanks Marco, this is actually what I meant by "purging": deleting the records that are no longer synchronised with the actual state od the DB.

So, if I inderstand correctly, there is no other way than recreating all the schemas and table?

This is quite annoying cause I even have the opposite case now: a table that exists in Oracle (even has a shape column) but that is no longer displayed in ArcGIS... The problem probably arose while I had to delete and re-create this table a few times (sometimes within Oracle, sometimes within ArcGIS). If I rename this table, it appears in ArcGIS. But as soon as I give it its real name back, it disappears... My assumption is that there must be a record in the ArcSDE schema saying that this table is invalid or something...

Any idea?

Re: Some ghost tables in ArcSDE (Oracle)

Anonymous User — Mon, 11 Nov 2013 08:23:46 GMT

Original User: mboeringa2010

This is quite annoying cause I even have the opposite case now: a table that exists in Oracle (even has a shape column) but that is no longer displayed in ArcGIS... The problem probably arose while I had to delete and re-create this table a few times (sometimes within Oracle, sometimes within ArcGIS). If I rename this table, it appears in ArcGIS. But as soon as I give it its real name back, it disappears... My assumption is that there must be a record in the ArcSDE schema saying that this table is invalid or something...

Any idea?

This is bad practice, you shouldn't delete tables registered with your geodatabase using Oracle tools. Use ArcGIS to delete datasets in any case where data is registered with the geodatabase, as ArcGIS will automatically handle the removal of metadata records in the geodatabase system tables in the ArcSDE Repository.

It really sounds you have a host of issues in your geodatabase. It may be better to start with a clean bill, by creating a new database, and than copy & paste any good datasets from the corrupted database to the new one. This will leave the junk in the old database, which you can delete afterwards.

Of course, if it is a large database with many layers, users and permission settings, this may not be an easy solution, in which case you may need to contact ESRI Tech Support to clean out your database and restore ArcSDE (Geodatabase) Repository integrity.

Re: Some ghost tables in ArcSDE (Oracle)

StéphaneHenriod — Mon, 11 Nov 2013 08:28:19 GMT

Yes, I now realize this isn't exactly good practices! Learning (and suffering) by doing 🙂

Recreating a DB is also not an option... I will thus contact ESRI support and see whether they can help at this point.

Thanks a lot!