Hi all,
I want to know the list of all tables names within Oracle schema. I tried : "select * from tab;" but it returns no row selected, although when I describe a specific table with : desc admin.DataTable .. I got a describtion of DataTable table!!.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi,
Select * from tab;
The above statement will give you table names for that schema only. If you have no tables in your schema then it will show "no row selected".
When you execute : desc admin.DataTable
Probably you have select previlege granted on DataTable of admin schema.
Make sure you have tables in your schema.
Just to expand on angels post:
Oracle has created many canned views on the base tables so you really don't ever have to use the base tables like 'tab'.
There are 3 levels of these views:
USER -- Just what I own
ALL -- Everything I can see with my current privs.
DBA -- everything
These are commonly followed by the object type you are going after:
INDEXES
TABLES
OBJECTS
VIEWS
etc...
Putting it all together:
If you connect as the schema owner, admin: select table_name from user_tables;
Connected as a privledged user:
select table_name from dba_tables where owner='ADMIN';
To add to what angelIII has given
You can give
select table_name from user_tables;
and this will give you the list of tables owned by the user executing that command
You can give
select owner,table_name from all_tables
and will give you the list of tables owned by the user executing the command as well as tables of other owners who have granted access to this user on those tables
and
select owner, table_name from dba_tables
will give you all the tables of all the owners
The last 2 commands require you to have administrative privileges
Yes, it is true that any Oracle user can select from: "all_tables" and "user_tables", but a select from "user_tables" could easily return no rows, if the user in fact owns no tables. Also, the "all_tables" view could return very few rows if the user has not been explicitly granted select privileges on any tables either directly or via a role. A select from the "all_tables" view will likely return the dual table, which usually has select granted to public, but it may not include any other tables.
Business Accounts
Answer for Membership
by: angelIIIPosted on 2005-10-13 at 04:28:12ID: 15076050
you have to be connected as admin user to get the table listed in tab.
to see all tables, check out this:
select table_owner, table_name from dba_tables
http://www.ss64.com/orad/D