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 queryselect * 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 fromDBA_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;

ALL_TABLE
- If you want to display the tables from specific database only then follow the below command.
SELECT * FROM USER_TABLES
ORDER BY TABLE_NAME;

USER_TABLE
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
andTABLE
fromDBA_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 fromDBA_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
andALL_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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.