How to get list of all tables in oracle

Last Updated on :February 6, 2020

You often want to query an oracle database to list out all the tables in a database. In Oracle, there are mainly 3 different types of views as mentioned in below. You can query those view to display names of the table in it.

DBA_TABLES

DBA_TABLES describes all relational tables in a database

SELECT owner, table_name
FROM DBA_TABLES

If you don’t have access to DBA_TABLES data dictionary view you will get error “ORA-00942: table or view does not exist” You can request DBA to grant you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role . After granting any one of the privilege you will be able to query any data dictionary view

ALL_TABLES

ALL_TABLES describes all tables accessible to current user. If you don’t have access to DBA_TABLES you can see all the tables that your current user has access to through ALL_TABLES View

SELECT owner, table_name
FROM ALL_TABLES

USER_TABLES

USER_TABLES describes all tables owned by the current user. You can query USER_TABLES view to know the list of tables that current user own.

This view does not have owner column because by default current user is owner

SELECT table_name
FROM USER_TABLES