Summary: in this tutorial, you will learn step by step how to show tables in the Oracle Database by querying from the data dictionary views.
If you have worked with MySQL, you may be familiar with the SHOW TABLES command that lists all tables in a database:
SHOW TABLES;Code language: SQL (Structured Query Language) (sql)Unfortunately, Oracle does not directly support the SHOW TABLES command. However, you can list all tables in a database by querying from various data dictionary views.
Show tables owned by the current user
To show tables owned by the current user, you query from the user_tables view.
SELECT table_name
FROM user_tables
ORDER BY table_name;
Code language: SQL (Structured Query Language) (sql)Note that this view does not show the OWNER column. Also, the user_tables table does not contain the other tables that are accessible by the current user.
Show tables that are accessible by the current user
To show all tables that are currently accessible by the current user, regardless of owners, you query from the all_tables view:
SELECT table_name
FROM all_tables
ORDER BY table_name;Code language: SQL (Structured Query Language) (sql)If you want to show all tables of a specific owner, you add the OWNER column in the WHERE clause as shown in the following query:
SELECT *
FROM all_tables
WHERE OWNER = 'OT'
ORDER BY table_name;Code language: SQL (Structured Query Language) (sql)Show all tables in the Oracle Database
To show all tables in the entire Oracle Database, you query from the dba_tables view as follows:
SELECT table_name
FROM dba_tables;Code language: SQL (Structured Query Language) (sql)You will get the following error message if you don’t have access to the dba_tables view:
ORA-00942: table or view does not exist
Code language: SQL (Structured Query Language) (sql)In this case, you should request your database administrator to grant your account privileges on the dba_tables view, or SELECT ANY DICTIONARY privilege, or SELECT_CATALOG_ROLE privilege.
The following picture illustrates the tables that can be returned from the user_tables, all_tables, and dba_tables views:
In this tutorial, you have learned how to show tables in the Oracle Database by querying from the data dictionary views including user_tables, all_tables, and dba_tables.
