CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
toop
toop

Reputation: 11294

Oracle - all_tables is not complete?

How come all_tables does not show actually contain a list of all the tables?

I can do select * from blah; and desc blah. But doing select * from all_tables where lower(table_name) = 'blah'; returns 0 rows.

blah is not a synonym or view but a table.

Is there a specific stats command that needs to be run so that all the tables that my user can query appear in all_tables?

Upvotes: 5

Views: 7034

Answers (3)

Big Ed
Big Ed

Reputation: 1244

'BLAH' is probably a synonym that references a table or view of a different name. You might try the following statement to see what's going on.

select owner, object_name, object_type 
    from all_objects 
    where object_name = 'BLAH'
union
select o.owner, o.object_name, o.object_type
    from all_synonyms s, all_objects o
    where synonym_name like 'BLAH'
      and o.owner = s.table_owner
      and o.object_name = s.table_name;

I ran it on my system and saw this output (names changed to protect the indigent).

OWNER   OBJECT_NAME    OBJECT_TYPE
------- -------------- -----------
PROD    T_BLAH         TABLE
PUBLIC  BLAH           SYNONYM

HTH.

Upvotes: 4

M Faisal
M Faisal

Reputation: 41

I am sure there is a confusion with object name. Please check in DBA_OBJECTS with the object name

USER_TABLES- tables owned

ALL_TABLES - tables owned, tables owner by other users with access to you

DBA_TABLES - all tables in the database

Upvotes: 1

Robert Brown
Robert Brown

Reputation: 11016

Try disconnecting and reconnecting your session and see if the result changes.

Upvotes: -2

Related Questions