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
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