Find table which has no index in Oracle

Check the table which has no index present in Oracle

Check table has no index for all the schema present in Oracle Database except sys or system

select 	OWNER,TABLE_NAME
from 
(
select 	OWNER,TABLE_NAME 
from 	dba_tables
minus
select 	TABLE_OWNER, 
TABLE_NAME 
from 	dba_indexes
)
Info_noindex
where	OWNER not in ('SYS','SYSTEM')
order 	by OWNER,TABLE_NAME;

Find the table which have no index present for particular schema or owner

select 	OWNER,TABLE_NAME
from 
(
select 	OWNER,TABLE_NAME 
from 	dba_tables
minus
select 	TABLE_OWNER, 
TABLE_NAME 
from 	dba_indexes
)
Info_noindex
where	OWNER = 'HR'
order 	by OWNER,TABLE_NAME;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.