• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » How to List All Tables in Oracle

How to List All Tables in Oracle

July 28, 2019 by techgoeasy Leave a Comment


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 purpose. Some time they want to List All Tables in particular tablespace or particular schema or sometime 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 oracle database are stored in SYS.TAB$ and SYS.COL$ tables.  Oracle has provided data dictionary views  to get the information about table 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.

To list all tables owned by the current user

select tablespace_name, table_name from user_tables; 

People familiar with 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 oracle database

To list all tables in a 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 list of tables in  schema in Oracle/oracle show tables in 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 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 the various tables, we need to find all the tables which have 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.

Related Articles

how to find indexes on a table in oracle
object statistics are locked
Query to check table size in Oracle
How to check Tablespace in Oracle
All_Tables reference from Oracle documentation


Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • Query to check table size in Oracle database
  • how to check all constraints on a table in oracle
  • Oracle Indexes and types of indexes in oracle with example
  • ORA-01017 Oracle Error Resolution Tips



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us