Oracle Database - Queries doing Full Table Scan - Last 15 minutes

In this blog, I am writing down a query to find out queries doing Full Table scan in last 15 minutes in Oracle Database.

It can be extrapolated to do queries doing full table scan in 30 / 60 or x minutes

Below is the query 

I am taking out queries from v$active_session_history (hoping the queries were sampled doing a full table scan)

and then joining the table with V$sql_plan.

I am making double sure using sql plan that the query is doing a full table scan using V$sql_plan view. 

set pages 500 lines 500

with sql_queries as 

(

select distinct sql_id from V$active_session_history

where sample_time > sysdate - 1/96   -- 1/24 for 1 hour

and sql_plan_options = 'FULL'

)

select distinct sp.sql_id from V$sql_plan sp, sql_queries sq

where sp.sql_id = sq.sql_id

and  sp.operation = 'TABLE ACCESS'

    AND sp.options = 'FULL';

 and sp.object_owner not in ('SYS','XDB','SYSTEM','DBSNMP')

;

I have also put a where clause to restrict sys,system,xdb tables, generally dbsnmp will not have any ownership of tables.

This will give distinct sql ids with table access full operation in last 15 minutes