Category Archives: Oracle Database Structures

Display All The Oracle Tables In The Database

 

Display All Oracle Tables in the database.  Shows all tables(less the system tables.   Change for your databases.  With where statement like  "where owner in 'YOUROWNERNAME'"

 

 

set lines 200
set pages 200
select owner, table_name, table_type, tablespace_name 
from dba_all_tables 
where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS') 
order by owner, table_name, tablespace_name

 

 

Display All The Oracle Indexes In The Database

 

Display All Oracle Indexes in the database.  Shows all indexes.   Customize for your database.

set lines 200
set pages 200
Select owner, index_name, table_type, tablespace_name 
From dba_indexes 
where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS') 
order by owner, index_name, tablespace_name
;