Show Tables?

How do you run a query to show all table in Oracle?
aedeanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ddandekarCommented:
A  view all_tables which can show required details of all tables. View description is as below :
Connected to:
Oracle8i Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> desc all_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(10)
 INSTANCES                                          VARCHAR2(10)
 CACHE                                              VARCHAR2(5)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)

SQL>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baonguyen1Commented:
ddandekar,

You can use ALL_TABLES view but this view shows only the tables to which the user has rights. If you have DBA right, use the DBA_TABLES view.

SQL>select table_names, owner from dba_tables

Hope this helps
0
MathiasMagnussonCommented:
Even without ful DBA access you can use the view dict. One benefit is that it lists both tables and views, so you don't have to look in xxx_tables and xxx_views to see both.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DanielztCommented:

You can query these views:

user_tables -- user owned tables
all_tables -- all tables user has access
dba_tables -- all relataional tables
dba_all_tables --all relataional tables  and object tables
dba_object_tables -- object tables
dba_catalog  -- tables,views, synonyms and sequences
0
n4nazimCommented:
Hi,

if u are connected to SYS or SYSTEM user u can say :

SELECT * FROM DBA_TABLES.

However if u r looking out for tables owned by a specific user .. u can say

SELECT * FROM DBA_TABLES WHERE OWNER = "owner_name';


If are not connected to SYS or SYSTEM and are connected thru some different user ( the one which u might be using to connect yr application ). use:


SELECT * FROM ALL_TABLES;

This shd give u list of all tables.


Hope this helps,
Rgds,
NHM
0
apolozovCommented:
select table_name from user_tables ;  - to have a list of tables you own
select table_name from all_tables    ;  - to have a list of tables you can select (yours + you are granted to select)
select table_name from dba_tables   ;  - to have a list of all tables in the db (if you're granted to see them)

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.