How to List All Tables in Oracle

DBA’s or the Developer often need to list various tables in oracle. They would require to List All Tables in Oracle for Audit and security purposes. Sometimes they want to List All Tables in a particular tablespace or particular schema or sometimes to list all the tables which are created recently. We would be showing all these in this post.

All the table and column information in the oracle database is stored in SYS.TAB$ and SYS.COL$ tables. Oracle has provided data dictionary views to get information about tables and columns.

There are three categories of views

List All Tables in Oracle with these data dictionary views as per the access you have

All the listing of the tables can be done from the above dictionary views.

Table of Contents

To list all tables owned by the current user/oracle show tables

select tablespace_name, table_name from user_tables;

People familiar with the MySQL database will know that We use show tables to list the tables in MySQL. So the above query is basically the oracle show tables in the oracle database

To list all tables in an Oracle database/ show all tables in oracle

select owner,table_name,tablespace_name, from dba_tables;

To list all tables accessible to the current user

 select owner, tablespace_name, table_name from all_tables;

Get the list of tables in a schema in Oracle/oracle show tables in the schema

select owner, tablespace_name, table_name from dba_tables where owner='&schema';

Query for oracle list tables in tablespace

select owner, tablespace_name, table_name from dba_tables where tablespace_name='&tablespace_name';

how to find the schema of a table in oracle

set lines 140 pages 1000
col table_name for a30
col owner for a7
select owner,table_name,initial_extent,ini_trans,freelists from dba_tables where table_name=upper('&tab_name');

How to find the creation date of a table in oracle

select owner,object_name,object_type,status,
to_char(last_ddl_time,'DD-MON-YY HH24:MI:SS') DDL_TIME,to_char(created,'DD-MON-YY HH24:MI:SS') CR_DATE
from dba_objects
where object_name =upper('&obj_name') and owner='&OWN' and object_type='TABLE',
/

How to find a recently created list of tables

select owner,object_name,object_type,status,
to_char(last_ddl_time,'DD-MON-YY HH24:MI:SS') DDL_TIME,to_char(created,'DD-MON-YY HH24:MI:SS') CR_DATE
from dba_objects
where created > sysdate -7 and  object_type='TABLE'
/

List All Tables In An Oracle Database By Column Name

Sometimes when doing the analysis of the schema i.e relations between the various tables, we need to find all the tables which have the same column names. The below query can be used to help in this. We can use the view according to the grants we have in hand

select owner,table_name from all_tab_columns where column_name = '&col_name';
 or
 select owner, table_name from dba_tab_columns where column_name = '&col_name';

We can many more queries built in the same way as per the need. The queries which I have presented above are the most asked ones and they are most often used. Please do let me know if you want to include any more queries in the list which will help people in the tasks.