Get list of all tables in Oracle?
How to List All Tables in an Oracle Database

Discover various SQL queries to retrieve a comprehensive list of tables in your Oracle database, including user-owned, all-accessible, and DBA views.
When working with Oracle databases, a common task is to identify all available tables. Whether you need to inspect your own schema, view tables accessible to your user, or get a complete picture of all tables in the entire database as a DBA, Oracle provides several data dictionary views to facilitate this. This article will guide you through the most common and effective methods to list tables, explaining the nuances of each approach.
Understanding Oracle Data Dictionary Views
Oracle's data dictionary is a set of read-only tables and views that contain metadata about the database. These views are crucial for querying information about database objects, including tables, indexes, users, and privileges. For listing tables, three primary views are commonly used, each offering a different scope of visibility:
USER_TABLES
: Shows all tables owned by the currently logged-in user.ALL_TABLES
: Shows all tables accessible to the currently logged-in user, including those owned by other users for which the current user has been granted privileges.DBA_TABLES
: Shows all tables in the entire database, regardless of owner or privileges. This view is typically only accessible to users with DBA privileges.
flowchart LR A["Logged-in User"] --> B{"Query Tables"} B --> C{"USER_TABLES"} C --> D["Tables owned by current user"] B --> E{"ALL_TABLES"} E --> F["Tables accessible to current user"] B --> G{"DBA_TABLES"} G --> H["All tables in database (DBA only)"]
Scope of Oracle Data Dictionary Views for Tables
Listing Tables Owned by the Current User (USER_TABLES
)
The USER_TABLES
view is the simplest way to see tables that you, as the current user, have created. It's ideal for quickly checking your own schema without seeing other users' objects.
SELECT table_name
FROM user_tables;
Query to list tables owned by the current user
Listing All Accessible Tables (ALL_TABLES
)
If you need to see tables that you can interact with, even if they are owned by other schemas, ALL_TABLES
is the view to use. This includes tables for which you have been granted SELECT
, INSERT
, UPDATE
, or DELETE
privileges, or tables in schemas that have been granted to you.
SELECT owner, table_name
FROM all_tables
ORDER BY owner, table_name;
Query to list all tables accessible to the current user, including their owners
ALL_TABLES
by owner
to see tables belonging to a specific schema, for example: WHERE owner = 'HR'
.Listing All Tables in the Database (DBA_TABLES
)
For database administrators or users with appropriate privileges, DBA_TABLES
provides a comprehensive list of every table in the entire Oracle database. This view is essential for auditing, schema management, and understanding the full database structure.
SELECT owner, table_name, tablespace_name
FROM dba_tables
ORDER BY owner, table_name;
Query to list all tables in the database, including owner and tablespace
DBA_TABLES
requires SELECT_CATALOG_ROLE
or direct SELECT
privilege on the view. If you encounter an 'ORA-00942: table or view does not exist' error, you likely do not have the necessary permissions.Additional Information and Filtering
Beyond just the table name, these views contain a wealth of other information that can be useful. You can select additional columns to get more details about each table, such as num_rows
, last_analyzed
, status
, and tablespace_name
.
For example, to get more details from USER_TABLES
:
SELECT table_name, num_rows, last_analyzed, status
FROM user_tables
WHERE tablespace_name = 'USERS'
ORDER BY table_name;
This allows for more targeted queries and deeper insights into your database schema.