Get list of all tables in Oracle?

Learn get list of all tables in oracle? with practical examples, diagrams, and best practices. Covers sql, oracle-database development techniques with visual explanations.

How to List All Tables in an Oracle Database

Hero image for Get list of all tables in Oracle?

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

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

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.