How to List All Tables in Oracle

All the table and column information 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

create table from another table without data

To list all tables owned by the current user,

select tablespace_name, table_name from user_tables; 

To list all tables in a Oracle database

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

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 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 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 created > sysdate -7 and  object_type='TABLE'
/

Related Articles

how to find indexes on a table in oracle

How to find table where statistics are locked

Query to check table size in Oracle

Tablespace Name ,Size ,Free space,datafiles ,growth in Oracle Database

Leave a Reply