How to Show Tables in Oracle Database

To list all tables in Oracle, you can query data dictionary views such as USER_TABLES, ALL_TABLES, or DBA_TABLES. These views provide information depending on your access level: the tables you personally own, those you can access with granted permissions, and the complete list of tables across the entire database, respectively. You can also use SQL*Plus commands or graphical tools like dbForge Studio for Oracle to inspect tables and their structure.

This guide walks through each method, from querying system views to exploring tables visually in a database IDE.

List tables in Oracle using data dictionaries

You can use Oracle's built-in data dictionaries to list tables available to different users. These views are read-only system tables that Oracle automatically maintains and updates whenever changes occur in the database. They are similar in concept to the INFORMATION_SCHEMA views in other database systems, but tailored to Oracle. Depending on the scope of access you need, Oracle offers several views that range from showing only your own tables to presenting every table in the database:

• USER_TABLES: Shows all tables owned by the currently logged-in user. This is useful when you want to quickly check only the objects you created in your schema.
• ALL_TABLES: Lists all tables the current user can access, regardless of ownership. This includes tables owned by other users if you have been granted privileges such as SELECT.
• DBA_TABLES: Provides a complete list of all tables in the entire Oracle database. Because of its scope, this view requires elevated privileges such as SELECT ANY DICTIONARY or the DBA role.

These views make it straightforward to understand what objects you own, what you can work with, and what exists in the database overall.

Show all tables owned by the current user

In practice, you often need to list all tables owned by the current user; it is one of the most common tasks in database management. Therefore, all major DBMSs offer the means to perform this task, such as the SQL Server list tables command in Microsoft SQL Server. In Oracle, the equivalent is referring to the USER_TABLES view.

This data dictionary contains only the tables that belong to your schema, so it's the most straightforward way to see your own objects.

SELECT * 
FROM user_tables;

Oracle enforces privilege levels, so you must be connected under the user whose tables you want to query. If you need to list tables for a different user, you must log in with that account.

Show all tables accessible to the current user

To find all tables you can access in Oracle, use the ALL_TABLES view. This data dictionary returns a list of tables available to you, regardless of ownership. The results include your own tables as well as those owned by other users, provided you have the required privileges. Permissions directly affect visibility here. For example, you need at least SELECT rights on another user's table for it to appear in the list.

SELECT table_name
FROM all_tables;

You can also extend the query to show the owner of each table and improve readability by ordering the output:

SELECT table_name, owner
FROM all_tables
ORDER BY table_name ASC;

This is especially useful when you need to quickly confirm which tables you can query across multiple schemas.

Show all tables in an Oracle database

The DBA_TABLES data dictionary provides the most comprehensive information about all tables in an entire Oracle database, irrespective of their owners. However, this view is accessible to the SYSTEM users or users with special privileges only. If your work duties require working with DBA_TABLES, you need the following privileges granted:

• The DBA_TABLES view privilege
• The SELECT ANY DICTIONARY privilege
• The SELECT_CATALOG_ROLE role

Then you can query DBA_TABLES as follows:

SELECT table_name
FROM dba_tables;

Use the SQL*Plus DESCRIBE command

To examine the structure of a table in SQL*Plus, use the DESCRIBE command.

What DESCRIBE does

DESCRIBE retrieves detailed information about a specific table or view, including column names, data types, and whether a column accepts NULL values. It is one of the simplest ways to explore schema objects without writing a complete query.

Syntax example

SQL> DESCRIBE table_name;

Or in shortened form:

SQL> DESC table_name;

Where and when to use it

The command is available in SQL*Plus, Oracle's command-line utility (also accessible in web-based SQL*Plus). Developers and DBAs commonly use it to review table definitions when designing queries quickly, debugging schema issues, or validating object structures.

Example

SQL> DESC customer;

This will return all column names in the customer table along with their data types.

Show all tables in an Oracle database with dbForge Studio for Oracle

We used dbForge Studio for Oracle to demonstrate the execution of commands against our databases. The Studio is an IDE for database development, management, and administration favored by numerous database experts for its powers and flexibility.

You can list tables in the Oracle database without typing any commands at all. Open dbForge Studio for Oracle, connect to the required database, and expand Tables in Database Explorer.

You can edit and manage tables visually directly from there. Right-click the necessary table and view the available options.

View tables in Object Viewer

To inspect the table structure or properties visually in dbForge Studio for Oracle, use Object Viewer. It provides a graphical way to explore database objects without writing queries. You can access it directly from Database Explorer:

• 1. Connect to the required database.
• 2. Expand the Tables node.
• 3. Right-click a table and select Show Details.

Object Viewer will display information such as table columns, data types, indexes, constraints, and relationships. Unlike querying system views, this approach offers an immediate, user-friendly view of the table's structure. It's particularly helpful for developers and administrators who prefer visual inspection, need to understand complex schemas quickly, or want to verify table properties before making changes.

Conclusion

Working with Oracle demands an understanding of the specifics of both this database system and PL/SQL. And then you have GUI-based database management solutions, such as dbForge Studio for Oracle, which are extremely helpful for Oracle specialists of all skill levels.

dbForge Studio for Oracle is highly functional, fast, and intuitive. Its ability to interact with databases directly through visual elements simplifies all tasks for you and doubles your efficiency, accuracy, and productivity. With a full-featured free trial available for 30 days, you can test dbForge Studio for Oracle within your workflow to properly assess its potential.