How to List All Tables in Oracle?

In this article, we will discuss all the methods to list all tables in the oracle SQL Database.

We have three types of a subset of tables available to use as identifiers which in turn help us to sort the required table names. Here, are the following types of table identifiers in the Oracle SQL Database.

1. DBA_tables:

If the user is SYSTEM or has access to dba_tables data dictionary view, then use the given below query:

Query:

SELECT owner, table_name FROM dba_tables;

This query returns the following list of tables that contain all the tables that are there in the entire database.

Output:

2. All_tables:

If the user does not have access or privilege to view the dba_tables it can still get a list of all the tables that it has access to using the following SQL query. This SQL query gives the list of tables that can be accessed by the user along with its owner.

Query:

SELECT owner, table_name FROM all_tables;

This query returns the following list of tables that contain all the tables that the user has access to in the entire database.

Output:

3. User_tables

If the user wants the list of all tables owned/created by him only, then use the following SQL query to get a list of tables. The following query does not return the name of the owner as it is the user itself for all the tables.

Query:

SELECT table_name FROM user_tables;

This query returns the following list of tables that contain all the tables owned by the user in the entire database.

Output: