Home > Tutorial > oracle > List all tables in Oracle Database Query

List all tables in Oracle Database Query

We can get a list of all tables in Oracle database in three ways depending upon the user privileges.

  1.  List all Tables owned by current Oracle user
  2.  List all Tables can be accessed by current user
  3.  List all Tables in entire Oracle database

 

We can list or show all table in Oracle by querying Oracle Data Dictionaries. A data dictionary in Oracle is a group of read-only tables that provide useful information to users about the database like schemas, users, privileges etc.

List all Tables owned by current Oracle user:

 

The below query returns a list of all tables owned by current user.

SELECT table_name FROM user_tables;

List all Tables can be accessed by Current user:

 

The below query lists all tables accessible to the current user.

SELECT owner, table_name FROM all_tables;

Owner column displays the owner name of the table. user_tables table does not have owner column.

List all Tables in entire Oracle database:

 

To get a list of all tables in entire database use below query.

SELECT owner, table_name FROM dba_tables;

However, you might get “ORA-00942: table or view does not exist” error if you do not have access to dba_tables. You should ask your database administrator to explicitly grants you privileges on that table, or SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (both of them of which allows you to query any data dictionary table).

dba_tables contains information of all the tables in current database. Whereas all_tables can be considered as a subset of dba_tables that has access to the current user. user_tables contains the information about tables owned by current user.

List All Tables In Oracle
List All Tables In Oracle

 

 

Wait before leaving. why can’t you follow me on twitter or be a friend on Facebook or googlePlus or linkedn to get in touch me. or join our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Arunkumar Gudelli
I am “One among a million” Software engineers of India. I write beautiful markup.I make the Web useful. You can connect me via @twitter or @facebook or Google+ or e-mail.
http://www.arungudelli.com

Lets have chat