How to show tables in Oracle Database

When working with databases, you often need to check tables in your databases, access certain tables or list all existing tables. Database management systems provide means to perform these tasks easily.

For example, MySQL has the SHOW TABLES command; however, other DBMSs do not support that function directly. They have alternatives, though. SQL Server offers its catalog views, and Oracle shows tables when you query its data dictionaries.

This article will review various options to query Oracle to show database tables:

List tables in Oracle using data dictionaries
Show all tables owned by the current user
Show all tables accessible by the current user
Show all tables in an Oracle database
Use the SQL*Plus DESCRIBE command
Show all tables with dbForge Studio for Oracle
View tables in Object Viewer

List tables in Oracle using data dictionaries

Data dictionaries are sets of specific tables that provide information about their associated databases. This concept is similar to information_schema in SQL Server.

These tables are read-only – Oracle maintains them and updates their contents whenever the data is modified on the server.

To list database tables, you can query the following data dictionaries:

USER_TABLES – the list of all tables owned by the current user.
ALL_TABLES – the list of all tables accessible to the current user.
DBA_TABLES – the list of all tables in the database.

Show all tables owned by the current user

Oracle allows different levels of accessibility for different users. That's why you need to make sure you have the necessary privileges before querying the necessary data dictionary.

The USER_TABLES data dictionary contains the information about the data you own. To list all tables belonging to some user, you need to log in to that user's account and query the USER_TABLES data dictionary:

SELECT * 
FROM user_tables;

This query brings the list of all tables owned by the current user from the entire Oracle database. An Oracle user owns one schema. Different users will have different lists of such tables.

Show all tables accessible by the current user

Database users often need to work with objects belonging to other users. If you have the privileges to access other users' tables (at least, a SELECT permission), you can query the ALL_TABLES data dictionary. It returns the list of tables you can use, no matter who owns them.

SELECT table_name
FROM all_tables;

You can modify this query to retrieve additional information. For example, add the table owner and order the output results for better readability:

SELECT table_name, owner
FROM all_tables
ORDER BY table_name ASC;

Using this query is helpful when you need to check which tables you can access, no matter if they belong to you or another user.

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 table 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 below:

SELECT table_name
FROM dba_tables;

Use the SQL*Plus DESCRIBE command

SQL*Plus is a command-line utility for Oracle, also available as a web interface. Besides providing specific commands that aren't available in other tools, SQL*Plus allows executing PL/SQL commands against the database.

One of its most popular commands is DESCRIBE, which is used to retrieve detailed information about a particular table or view. The command is as follows:

SQL> DESCRIBE table_name;

Or you can use the shortened format:

SQL> DESC table_name;

By default, the command applies to the current database (in our example, we use the sakila test database).

SQL> describe customer;

The command fetches the list of column names with their associated data types.

How to search a string in Oracle database

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 the Studio, connect to the required database, and expand the Tables directory in the Database Explorer on the left.

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

How to find table name from view in Oracle

View tables in Object Viewer

Another option available in dbForge Studio for Oracle is using Object Viewer

Right-click any object, such as a table in the Tables directory, and select View Details.

You can get the detailed information about each table.

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.

Availability in the editions of dbForge Studio for Oracle

Feature

Enterprise

Professional

Standard

Express

Object Manager