How to Get List all Tables in Oracle Database

In this Oracle tutorial, we will learn how to write a query to list all tables in oracle database. Also, we will cover these topics.

• Query to List all Tables in Oracle Database
• Query to List all Tables in Oracle Database Schema
• Query to Display all Tables in Oracle Database
• Query to List all User Tables In Oracle Database
• Query to Find List of Tables in Oracle Database
• SQL Query to List all Tables and Columns in a Oracle Database

Query to List all Tables in Oracle Database

In this section, we will learn how to write a query to list all tables in oracle database.

• Unlike other databases, there is no specific command to list all tables in the oracle database. We have to write a query to list all tables in the oracle database.
• DBA_OBJECTS describe all objects in the database, by passing query select * from DBA_OBJECTS you can view entire schema of the oracle database.
• There are variety of columns in DBA_OBJECTS that shows information related to the schema. One of the column is USER_TABLES, this keeps a record of all the tables created in the oracle database.
• Using ALL_TABLES option in oracle database we can list all tables in oracle database.

SELECT TABLE_NAME FROM ALL_TABLES

• Alternative, we can use fetch TABLE_NAME columns from DBA_TABLES.

SELECT TABLE_NAME FROM DBA_OBJECTS

This is how to query all tables in oracle database.

Read How to Check Oracle Database Version

How to List all Tables in Oracle Database Schema

In this section, we will learn how to write a query to list all tables in oracle database schema.

• Schema refers to the structure of the database. In other words, it explains the way data is organised within relational database.
• Using ALL_TABLES command in oracle database we can write query to list all tables in oracle database schema.

SELECT TABLE_NAME FROM ALL_TABLES

This is how to get list of all tables in Oracle database schema.

Read Connect Excel to Oracle Database

Query to Display all Tables in Oracle Database

In this section, we will learn how to write a query to display all tables in oracle database.

• Using ALL_TABLES command in oracle database we can display all the tables that are presently accessible by current user irrespective of the owners.
• Use the below query to display all tables in oracle database accissible by current user.

SELECT TABLE_NAME FROM ALL_TABLES;

• If you want to display the tables from specific database only then follow the below command.

SELECT * FROM USER_TABLES
ORDER BY TABLE_NAME;

This is the query to display all tables in Oracle Database.

Also, check: How to Fetch Data from Oracle Database in Python

How to List all User Tables In Oracle Database

In this section, we will learn how to write a query to list all user tables in oracle database.

• If you want to list all the tables belonging to a specific user then you apply the filter on OWNER column of the oracle database.
• In the below code, we are displaying OWNER and TABLE from DBA_OBJECTS where ‘C##SQLSERVERGUIDES‘ is the user in our case.

SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS
WHERE OBJECT_TYPE='TABLE' AND OWNER='C##SQLSERVERGUIDES';

• Another possible query to perform the same task is by fetch information from DBA_TABLES. Here is the query to list all user tables in oracle database.

SELECT OWNER, TABLE_NAME FROM DBA_TABLES
WHERE OWNER='C##SQLSERVERGUIDES';

This is how to list all user tables in oracle database.

Read How to create a database in Oracle 19c

Find List of Tables in Oracle Database

In this section, we will learn how to write a query to find a list of tables in oracle database.

• In oracle database we do not have specific command to display list of tables in a database. We have a wrie a query to display list of tables in oracle database.
• In our first section of this blog we have demonstrated ‘how to write query to list tables in oracle database’ similarly here we will show you another way of doing the same.
• In our first section we fetched the table_name from DBA_OBJECTS but here we will do it from DBA_TABLES.

SELECT TABLE_NAME FROM DBA_TABLES;

In this output, we have written a query to find a list of tables in the oracle database.

Read Number Datatype in Oracle Database

SQL Query to List all Tables and Columns in a Oracle Database

In this section, we will learn how to write a SQL query to list all tables and columns in an oracle database.

• There can be multiple queries to list all tables and columns in a oracle database. Tables and columns can be fetched from DBA_OBJECTS, DBA_TABLES and ALL_TABLES.
• You can specify the names of all the columns or use ‘*‘ to display all the tables and columns with entire data in oracle database.
• Using ALL_TAB_COLUMNS in oracle database you can list all tables and columns in a oracle database.
• Below we have displayed multiple sql queries to list all tables and columns in a oracle database.

# Method 1
SELECT * FROM DBA_OBJECTS;

# Method 2
SELECT * FROM DBA_TABLES;


# Method 3
SELECT * FROM ALL_TABLES;

# Method 4
SELECT * FROM ALL_TAB_COLUMNS; 

In this output, we have written SQL Query to List all Tables and Columns in an Oracle Database.

This is the query to list all tables and columns in an Oracle Database.

Also, check: Connect to oracle database

In this Oracle tutorial, we have learned to write query to list all tables in the oracle database. Also, we have covered these topics.

• List all Tables in Oracle Database
• How to List all Tables in Oracle Database Schema
• Display all Tables in Oracle Database
• List all User Tables In Oracle Database
• How to Find List of Tables in Oracle Database
• List all Tables and Columns in a Oracle Database