topic Re: ORA-00942 Without Compatibility Issue? in Data Management Questions

ORA-00942 Without Compatibility Issue?

Anonymous User — Wed, 18 Sep 2013 22:38:48 GMT

Hello! I have a 10.2 client trying to connect to a 10.2 Oracle geodatabase. The error received is:

Release of GeoDatabase invalid or out of date. ORA-00942: table/view doesn't exist.

I understand, from online resources, that this is a common error with compatibility issues. But both client and gdb here are on 10.2.

I strongly suspect privilege issues, but cannot currently prove or disprove this theory. I also have a second 10.2 Oracle geodatabase to which this user can connect with no trouble at all.

What should I be looking for? I cannot ask the DBA to grant frivolous privileges, so SELECT ANY and EXECUTE DBMS* are not an option. The documentation shows CREATE SESSION is all that a read-only user requires, but there must be more that I'm missing.

One added bit of info: the user can create a Query Layer (10.0) and an ArcSDE Connection File (10.2). The error is only thrown when attempting a direct connect.

Thank you!

Re: ORA-00942 Without Compatibility Issue?

VinceAngelo — Wed, 18 Sep 2013 23:34:08 GMT

How can you create a 10.0 Query Layer with 10.2 Desktop?

- V

Re: ORA-00942 Without Compatibility Issue?

Anonymous User — Wed, 18 Sep 2013 23:39:40 GMT

How can you create a 10.0 Query Layer with 10.2 Desktop?

- V

You caught me abbreviating my thoughts! I have two computers, one running 10.0 sp5, the other 10.2. The Query Layer doesn't work in 10.2, either--it throws the same error.

Re: ORA-00942 Without Compatibility Issue?

VinceAngelo — Wed, 18 Sep 2013 23:44:20 GMT

And what exact Oracle releases are in use (A.B.C.D.E notation), both client and server?

- V

Re: ORA-00942 Without Compatibility Issue?

Anonymous User — Wed, 18 Sep 2013 23:48:51 GMT

Clients and server are running Oracle client 11.2.0.1.0.

Re: ORA-00942 Without Compatibility Issue?

VinceAngelo — Thu, 19 Sep 2013 00:01:36 GMT

This sort of issue is more effectively worked as part of a Tech Support incident.
Having a working and non-working instance at least gives you the ability to
look for differences in content, both of the SDE-owned tables, and of the
permission meta-tables.

- V

Re: ORA-00942 Without Compatibility Issue?

MarcoBoeringa — Thu, 19 Sep 2013 10:06:17 GMT

One added bit of info: the user can create a Query Layer (10.0) and an ArcSDE Connection File (10.2). The error is only thrown when attempting a direct connect.

This makes me curious...

Am I correct to assume that what you are saying here is:

- You can access some Feature Classes in 10.2 by a means of an ArcSDE Connection File and running ArcSDE Application Server.
- You can access these same Feature Classes by means of a Query Layer in ArcGIS Desktop 10.0
- You can't access these same Feature Classes by means of a Query Layer in ArcGIS Desktop 10.2
- You can't access these same Feature Classes through Direct Connect in 10.2

Are you sure you have installed a proper ODBC database driver on your Client PC that runs 10.2? Is the Oracle Client fully functional, can you execute SQL commands against your database?

Re: ORA-00942 Without Compatibility Issue?

VinceAngelo — Thu, 19 Sep 2013 10:35:51 GMT

I've encountered silent failures in geodatabase creation where the SDE tables required
for functional ST_GEOMETRY and ArcSDE 'C' API were present, but the GDB_* tables
were missing. Check your SDE user table inventory.

Query Layers should not care one iota about geodatabse tables. It seems odd that
an error message

ArcSDE does not use ODBC in Oracle (Informix only).

- V

Re: ORA-00942 Without Compatibility Issue?

MarcoBoeringa — Sat, 21 Sep 2013 18:16:03 GMT

ArcSDE does not use ODBC in Oracle (Informix only).

- V

Sorry, should have written "database client" instead of ODBC database driver... although for those not knowing it, it seems ESRI will support the Microsoft ODBC database driver for SQL Server as well for Direct Connect in a future release:

http://forums.arcgis.com/threads/86711-Direct-Connect-to-SQL-Server-2012-in-10.1?p=307267&viewfull=1#post307267
and
http://forums.arcgis.com/threads/86711-Direct-Connect-to-SQL-Server-2012-in-10.1?p=307517&viewfull=1#post307517

I agree Query Layers have nothing to do with geodatabase system tables in the ArcSDE Repository, but they do have some similar dependencies as Direct Connect on proper ArcGIS and database client installation / configuration.

Anyway, Marianne still needs to answer if my assumptions of post 7 were correct, might tell a bit more of what might be wrong.

Re: ORA-00942 Without Compatibility Issue?

Anonymous User — Thu, 26 Sep 2013 15:08:15 GMT

Apologies for the delayed response; I was out on an unexpected family matter.

This issue has evolved since I last posted. It was discovered that IT, in an emergency move of the production database to a new server, had not migrated all permissions and roles (including public) consistently. We also learned that IT's dev, test, and prod environments are not the same--either with regard to user permissions or content.

After sifting through and untangling these red herrings, the remaining problem is this:
The user I referenced in my original post cannot connect to the production database by any method except 10.0 Query Layer. This includes 10.0 SDE Service connection, 10.0 Direct Connect, 10.2 SDE Connection File, 10.2 Direct Connect, and 10.2 Query Layer. I know my original post indicated a 10.2 Connection File was successful; this inconsistency is a difficult trait of having a split shop--GIS on one side, IT on the other. They must have changed something, but I don't know what.

But here's the twist: the SDE user can connect to the production database via any method. Again, this was not true last I posted; something IT changed during this process made it so.

So, again, I am left to strongly suspect permissions as the root issue. I will post when the resolution appears.

Thanks again for your help.

Re: ORA-00942 Without Compatibility Issue?

MarcoBoeringa — Thu, 26 Sep 2013 15:52:32 GMT

But here's the twist: the SDE user can connect to the production database via any method. Again, this was not true last I posted; something IT changed during this process made it so.

So, again, I am left to strongly suspect permissions as the root issue. I will post when the resolution appears.

If the SDE user can access the same database from exactly the same client PC, and access the full (ArcMap) geodatabase functionality without issues, than I think you are right and we can exclude an issue with the ArcSDE Repository, and also a possible misconfiguration of the database client on the client PC.

The permission track you are on sounds right, especially in the light of all the other issues related to permissions and roles you highlighted in your last post.

You may find this Help page useful to track down the issue:

User privileges for geodatabases in Oracle

Re: ORA-00942 Without Compatibility Issue?

Anonymous User — Thu, 26 Sep 2013 16:10:48 GMT

Thank you, both for the thoughtful response and the link. I examined the link's content when this issue first arose, and our permissions all adhere to its documentation. Either the documentation is incomplete, or my IT division has done something which negates the granted permission. If I ever figure out which, I will update. So far, I am leaning toward the latter...