How to find SQL doing full table scans with row and block context in Oracle

Enriched version of full-table-scan hunting: pulls the SQL text from V$SQLAREA and the target table''s NUM_ROWS, BLOCKS, and BUFFER_POOL from DBA_TABLES so you can immediately see whether the scan is big or trivial.

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) AS sqltext,
       (SELECT executions
        FROM   v$sqlarea sa
        WHERE  sa.address    = sp.address
        AND    sa.hash_value = sp.hash_value) AS 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) AS "rows|blocks|pool"
FROM   v$sql_plan sp
WHERE  operation = 'TABLE ACCESS'
AND    options   = 'FULL'
AND    object_owner NOT IN ('SYS', 'AUDSYS', 'SYSTEM', 'DBSNMP')
ORDER BY 1, 2;