Getting all tables from an oracle database

Question

I'm trying to retrieve a list of tables from an Oracle connection. I'm not very familiar with Oracle terminology and thus, having hard time finding the information I need.

Right now I can use Microsoft Access to connect via ODBC and it pops up with a "Link Tables" dialog that lists all tables, not just the ones I "own". None of the queries I've tried so far, give me this data.

I'm trying "SELECT * FROM all_tables" but that doesn't show me the right data.

possible duplicate of [Oracle: get list of all tables?](http://stackoverflow.com/questions/205736/oracle-get-list-of-all-tables) — Justin Cave, Apr 02 '12 at 16:13
Please explain *"doesn't show me the right data*". What do you see? What do you expect? — a_horse_with_no_name, Apr 02 '12 at 16:17

score 3 · Accepted Answer · answered Apr 02 '12 at 16:15

3

ALL_TABLES will show you all the tables that you have access to SELECT from. DBA_TABLES will show you all the tables that exist in the database though you'll need an additional privilege grant to be able to query the DBA* data dictionary objects.

answered Apr 02 '12 at 16:15

Justin Cave

221,607
22
353
373

1

It turns out some of the tables I was seeing were actually views and synonyms. The query `SELECT * FROM ALL_CATALOG` worked for my needs. – Brandon Wamboldt Apr 03 '12 at 12:46

score 1 · Answer 2 · answered Oct 16 '19 at 08:25

1

SELECT owner, table_name
  FROM all_tables

answered Oct 16 '19 at 08:25

Vasin Yuriy

453
5
13

score 1 · Answer 3 · edited Apr 02 '12 at 16:14

1

Try select * from all_tables, that should do what you want.

edited Apr 02 '12 at 16:14

Michael Fredrickson

36,074
5
88
107

answered Apr 02 '12 at 16:13

DoctorMick

6,553
25
24

score 1 · Answer 4 · answered Apr 02 '12 at 16:15

1

It can be.. (If user has dba role)

select * from dba_tables

answered Apr 02 '12 at 16:15

hkutluay

6,614
2
30
51

score 0 · Answer 5 · answered May 17 '14 at 10:43

0

SELECT * FROM TAB; that will show you all the table and views

answered May 17 '14 at 10:43

simplePerson43

3,629
1
14
10

score 0 · Answer 6 · answered Apr 02 '12 at 19:44

0

You can also try

SELECT * FROM USER_TABLES

It will return list of tables owned by your user.

answered Apr 02 '12 at 19:44

denied

578
3
9

Getting all tables from an oracle database

6 Answers6