Some ghost tables in ArcSDE (Oracle)

2307
11
11-07-2013 05:02 AM
StéphaneHenriod
New Contributor II
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
0 Kudos
11 Replies
by Anonymous User
Not applicable
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.
0 Kudos
StéphaneHenriod
New Contributor II
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
0 Kudos
by Anonymous User
Not applicable
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
0 Kudos
EmadAl-Mousa
Occasional Contributor III
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.
0 Kudos
by Anonymous User
Not applicable
Original User: vangelo

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

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

  2. would)
  3. It does not delete geodatabase metadata associated with the table ('sdetable -o delete'

  4. won't either).
You should always use ArcGIS to delete tables which have been manipulated by
ArcGIS or otherwise registered with the geodatabase.

- V
0 Kudos
StéphaneHenriod
New Contributor II
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?
0 Kudos
StéphaneHenriod
New Contributor II
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?
0 Kudos
by Anonymous User
Not applicable
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.
0 Kudos
StéphaneHenriod
New Contributor II
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?
0 Kudos