Oracle List Tables in Database | A Complete Guide

Asiri Gunasena

Published:

SQL

oracle list tables in database

Oracle provides many ways to retrieve a list of tables. Sometimes, we need to find all the Oracle List Tables in Database. This article will address similar operations like retrieving column names, total table count, Oracle SQL developer showing all Tables in the Database, and so on.

How to List Tables in Oracle Database

there are a lot of ways to see all the tables in a database. It is dependent on user privileges some users may not use all the queries for Oracle list tables in a database. Then check whether user privileges if you’re not allowed to use all the queries here.

We can use the below keywords to find Oracle list tables in a database.

  1. dba_tables view.
  2. all_tables
  3. User_tables

Oracle SQL Query to list all Tables in a Database

oracle list tables in database

dba_tables view.

This is the biggest option if you have admin privileges. This works only if you have SYSTEM users or users with special privileges only. Below query listing the entire database. This query lists all the tables including DBA_TABLES data dictionary view.

1st way of oracle list tables in database

--
SELECT
  table_name, owner
FROM
  dba_tables
--

all_tables

This is also able to retrieve all the tables in the database. But it depends on user has access to the entire database. If you do not have access to the DBA_TABLES data dictionary view, then you can use the below SQL to Oracle list tables in a database.

2nd way of oracle list tables in database

--
SELECT owner, table_name FROM all_tables;
--

user_tables

This is listing only user user-created table. You can’t see all the tables in the database just able to see tables all tables owned/created by him only. USER_TABLES data dictionary available data only to you. If you need to see some other user-created table, then have to lg with that user.

--
SELECT table_name FROM user_tables;
--

List all Tables and Columns in Oracle Database

Now you know how to list all the tables in a database then move to how to list all tables and columns in an Oracle database. You can see all the columns in the database but that is not giving good to see and understand. So I filter t single table like below.

A. If you have user privilege and a Table accessible to the current user

--
select *
from sys.all_tab_columns col
inner join sys.all_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
where col.table_name = 'My_Table' 
order by col.column_id;
--

B. If you have full database privilege on dba_tab_columns and dba_tables

--
select *
from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
where col.table_name = 'My_Table'
order by col.column_id;
--

Above are the queries to find columns in a database. Once you remove the table name filter you are able to find all the columns in table

Count Number of Tables in Oracle Database

I added the below queries because we have table names in the first query. If I would like to add simple queries to count the number of tables in the Oracle database, I use the same format we used in the Oracle list tables in the database section.

A. Tables access from the current user

--
select count(*) as table_count
from sys.all_tables;
--

B. Tables access with admin privileges on dba_tables

--
select count(*) as table_count
from sys.dba_tables;
--

Oracle SQL developer show all Tables in Database

There is an easy way to find all the tables and columns by using Oracle SQL Developer. You can see not only tables but also functions, indexes, and many more information. See the image and lg with the user, then you can see all the information.

oracle list tables in database

I also need to note that tables are only seen according to t your lg user privileges. Below is the same type of article you may like to see

REF: Link

Categories SQL
ennicode

Address: 89/1 Rabbegamuwa, Handessa, Kandy, Central, Sri Lanka

Email to: Primary: [email protected]

Services

E-Learning

Company Websites

Support and Configuration work

Banners, Covers, and Post

Web Development & Configurations

Content Writing and Marketing

Contact

Ennicode