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

Question

Hi,

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.

Answer 1

Just because you have access to the ALL_TABLES view does not mean you can actually select the tables you retrieve via this view. You need the appropriate grant select on the tables to select them.

2 tables can have the same name as long as they belong to a different schema (the scope is actually broader than just tables, a schema cannot have two objects (table, view ...) that have the same name)

Answer 2

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?

Answer 3

Yes, it is a security feature - if you do not have permission to SELECT from a table, Oracle will normally not even acknowledge to you that it exists (an ordinary user would not be able to see things in select from ALL_ unless that was granted by the DBA).