Identify FULL SCANS in Oracle 10.2.0 database

When you want to see what objects are the most often full scanned, use this:

select object_name,count(*) runs from dba_hist_sql_plan plansql,  dba_hist_sqlstat sqlstat
where operation='TABLE ACCESS' and options='FULL' and object_owner='<schema>'
and plansql.sql_id=sqlstat.sql_id
group by object_name
order by runs desc
/

Then, you can try to identify the most often issued sql statement:

select distinct sqlstat.sql_id, count(*) FOUND_IN_AWR,sqlarea.sql_text from dba_hist_sql_plan plansql,  dba_hist_sqlstat sqlstat, v$sqlarea sqlarea
where operation='TABLE ACCESS' and options='FULL' and object_name ='<found in part1>' and object_owner='<schema>'
and plansql.sql_id=sqlstat.sql_id
and plansql.sql_id = sqlarea.sql_id
group by sqlstat.sql_id,sqlarea.sql_text
order by FOUND_IN_AWR desc
/