Tables are the basic unit of data storage in Oracle databases.
In this note i will show how to list all tables in an Oracle database (equivalent to SHOW TABLES command in MySQL) using the command-line interface of SQL*Plus.
I will also show how to list only tables owned by the current user, show the tables that the current user has access to and how to show the tables owned by the particular user or in the particular schema.
Show Oracle “databases” (schemas/users) from the command-line using SQL*Plus. Get the current schema name. Switch to different schemas.
Cool Tip: Show Oracle “databases” (schemas/users) – equivalent to SHOW DATABASES command in MySQL! Read more →
Show Tables in Oracle
Show all tables in Oracle (requires privileges on dba_tables):
SQL> SELECT table_name
FROM dba_tables
ORDER BY table_name;
If the current user doesn’t have enough privileges, you may get the following error:
ORA-00942: table or view does not exist
List tables in Oracle that the current user has access to:
SQL> SELECT table_name
FROM all_tables
ORDER BY table_name;
Show tables owned by the current user:
SQL> SELECT table_name
FROM user_tables
ORDER BY table_name;
Show tables owned by the particular user or in the particular schema (that are essentially the same thing):
SQL> SELECT table_name
FROM all_tables
WHERE owner='<schema_name|user_name>'
ORDER BY table_name;