[Amit Bansal]

This is very straight forward SQL which can be used to identify objects on which Full Table Scan (FTS) is being performed.

select object_owner,object_name from V$SQL_PLAN where

operation=’TABLE ACCESS’ and

options=’FULL’ and

object_owner<>’SYS’;

Please note that this will work with queries cached in shared pool. In 10g and above, you can also query DBA_HIST_SQL_PLAN to get historical information.

[Author]

Posts