• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

Oracle 11g: sqlplus: though tables queried do exist, I get a 'table does not exist' when running query, why?

Hello Experts,

Oracle 11g: via sqlplus: connected to db successfully.

I run: SELECT table_name FROM all_tables;
I get all tables available....

However, when I try to simple query such:
SELECT * FROM tableName;
I get: table does not exist

is this a permission issue?
Is there a way via the command line to know what permissions I have?

  • 3
  • 2
2 Solutions
If you are in schema "A" and select table_name from all_tables, you will get the table_names in other schemas to which you have been granted access. So, for example, your first query returned a table_name of tab1, but it belonged to schema "B", when you select * from tab1 you will get the table or view does not exist message. However, you should be able to get results by qualifying the table_name with the schema - select * from B.tab1.
Steve WalesSenior Database AdministratorCommented:
Selecting from all_tables will show you all tables that the user you are connected to has access to see - not necessarily all the tables that user owns.

Try this instead:

select owner, table_name from all_tables;

Is the table you are trying to select from owned by another user ?

Taking the output from that query, you can then do:

select * from owner.tableName;

Try that, let us know if that resolves the issue.
Isn't that kind of what I said?
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Steve WalesSenior Database AdministratorCommented:
Look at the timestamps of the replies.  Less than 2 minutes between them.

I was typing mine while you were typing yours and you finished first.  When I started typing my answer there was no other reply there.
epifanio67Author Commented:
thank you experts... I really appreciate your help
And usually I'm the slow typist :-) No harm meant.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now