We can get a list of all tables in Oracle database in three ways depending upon the user privileges.
- List all Tables owned by current Oracle user
- List all Tables can be accessed by current user
- 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.
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