How to List All Tables in a Schema in Oracle Database?

Understanding the tables within a schema is essential for various database management tasks. Whether you’re performing a security audit, crafting queries, or simply exploring the schema structure, knowing which tables exist is a crucial first step.

Users can get a list of all tables in a schema in Oracle Database from the SYSOBJECTS view. Here, we will learn the query to use SYSOBJECTS view, and get list of tables.

Use the following query to list all tables in Oracle Database Schema.

SELECT * FROM SYSOBJECTS

This query can be modified to extract only relevant information about the tables, or fetch specific types of tables. Let’s cover different versions of the query, which shows how we can use use SYSOBJECTS view.

List Tables Created by User in Oracle Database

This method lists all the information regarding all the tables which are created by the user. The user clause is specified through the expression after WHERE keyword, i.e. XTYPE=’U’ (U stands for user).

Query:

SELECT * FROM SYSOBJECTS 
WHERE XTYPE='U';

Output:

Get Selective Information of All the Tables

This method lists only selective information regarding all the tables which are created by the user. The user clause is specified through the expression after WHERE keyword i.e. XTYPE=’U’ (U stands for user).

Here only the name(NAME), the creation date(CRDATE) and the last reference date(REFDATE) of the table are selected.

Query:

SELECT NAME,CRDATE,REFDATE 
FROM SYSOBJECTS WHERE XTYPE='U';

Output: