Traditionally speaking, full table scans are not welcome in an OLTP environment. This SQL script will help identify full table scans very easily as there is a 9i view that stores all executed statement plans. Prior to 9i, it would have been a bit more difficult to do this.
Please note that the output will also include something as simple as
SELECT * FROM DUAL
The script can be easily modified to pick out the more resource-intensive FTS by filtering and/or ordering by one of the selected columns or other columns in V$SQLAREA.
set pages 1000 set feedback off set lines 150 spool fts.lst Column sql_text Format A50 Heading 'Full Table Scans' Wrap break on sql_text skip 1 Select Executions, Sorts, Disk_Reads, Buffer_Gets, CPU_Time, Elapsed_Time, sql_text from v$sqlarea where (address, hash_value) in (Select address, hash_value from v$sql_plan where options like '%FULL%' and operation like '%TABLE%') Order by Elapsed_Time / spool off set feedback on