Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get list of all tables in Oracle?

Tags:

sql

oracle

How do I query an Oracle database to display the names of all tables in it?

like image 221
vitule Avatar asked Oct 15 '08 17:10

vitule


People also ask

How do I get a list of tables in Oracle?

SELECT owner, table_name FROM all_tables; This query returns the following list of tables that contain all the tables that the user has access to in the entire database.

How do I list all tables in a schema in Oracle SQL?

If you want to list all tables in the Oracle database, you can query the dba_tables view. SELECT table_name FROM dba_tables ORDER BY table_name ASC; This view (and all others starting with dba_) are meant for database administrators.


2 Answers

Querying user_tables and dba_tables didn't work.
This one did:

select table_name from all_tables   
like image 31
vitule Avatar answered Oct 13 '22 16:10

vitule


SELECT owner, table_name   FROM dba_tables 

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name   FROM all_tables 

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name   FROM user_tables 

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

like image 168
Justin Cave Avatar answered Oct 13 '22 16:10

Justin Cave