Find Full Table Scan Queries

This query provides a list of queries (1,000 characters only) involved in full table scans. The total number of rows and blocks for each table is also displayed so that you can determine whether the full scan is degrading performance.

SELECT   sp.object_owner, sp.object_name,

         (SELECT sql_text

            FROM v$sqlarea sa

           WHERE sa.address = sp.address

             AND sa.hash_value = sp.hash_value) sqltext,

         (SELECT executions

            FROM v$sqlarea sa

           WHERE sa.address = sp.address

             AND sa.hash_value = sp.hash_value) no_of_full_scans,

         (SELECT    LPAD (NVL (TRIM (TO_CHAR (num_rows)), ' '),

                          15,

                          ' '

                         )

                 || ' | '

                 || LPAD (NVL (TRIM (TO_CHAR (blocks)), ' '), 15, ' ')

                 || ' | '

                 || BUFFER_POOL

            FROM dba_tables

           WHERE table_name = sp.object_name AND owner = sp.object_owner)

                                                           "rows|blocks|pool"

    FROM v$sql_plan sp

   WHERE operation = 'TABLE ACCESS'

     AND options = 'FULL'

     AND object_owner IN ('USERNAME')

ORDER BY 1, 2;

Or the following query finds recently FULL TABLE Scan queries; means, index are missing:

SELECT *
FROM dba_hist_active_sess_history
WHERE sql_plan_options = 'FULL'
ORDER BY sample_time DESC

SELECT sql_text, parsing_schema_name, module
FROM v$sql
WHERE sql_id = 'drtn4c1accwrw'