Tables which is not having Indexes: -
select OWNER,TABLE_NAME
from (select OWNER,TABLE_NAME from dba_tables minus
select TABLE_OWNER, TABLE_NAME from dba_indexes)
orasnap_noindex
where OWNER NOT IN ('SYS','SYSTEM')
order by OWNER,TABLE_NAME;
Tables which is having more then 5 Indexes: -
select OWNER,TABLE_NAME,COUNT(*) index_count
from dba_indexes
where OWNER NOT IN ('SYS','SYSTEM')
group by OWNER, TABLE_NAME
having COUNT(*) > 5
order by COUNT(*) desc, OWNER, TABLE_NAME;
Columns which is having more then one Index: -
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME
from dba_ind_columns
where COLUMN_POSITION=1
and TABLE_OWNER not in ('SYS','SYSTEM')
group by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having count(*) > 1 ;
Columns which is having Primary Key: -
select OWNER, TABLE_NAME from dba_tables dt
where not exists
(select 'TRUE' from dba_constraints dc
where dc.TABLE_NAME = dt.TABLE_NAME and dc.CONSTRAINT_TYPE='P')
and OWNER NOT IN ('SYS','SYSTEM')
order by OWNER, TABLE_NAME;
