Search code examples
.netoracleschemaoracle11gora-00942

Problems When Querying Oracle Database For List Of Tables: "table or view does not exist"

In my program I need to access the schema of an Oracle 11g database. I have been trying to get a list of tables using a query like this:

SELECT t.TABLE_NAME, t.OWNER
FROM ALL_TABLES t
WHERE t.DROPPED = 'NO'
ORDER BY t.TABLE_NAME

The query works and I get back a list of tables. Unfortunately when querying some of the tables using the table name returned I get the following error:

ORA-00942: table or view does not exist

What could cause this error? Could it be down to privileges?

In a separate issue I am also a bit confused about whether there could potentially be two or tables with the same name and how I could distinguish between the two. Do I need to watch out for this?

I am using the Oracle.DataAccess provider in .NET to connect to the database. It is a remote server and unfortunately I have very limited access to it.

Solution

  • It could be down to privileges. But it could be down to the table being in a different schema, and there not being a synonym for it. Does it work if you qualify the table name with the owner? ie select from [owner].[table_name] instead?