Find the sql and tables which are performing full table scan (FTS)

1) Below query give number of full table scan of tables in last one day along with owner.

  SELECT 

     object_owner,

     object_name ,COUNT(*) FTS_NO

   FROM

 dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'TABLE ACCESS'

      AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND

      options = 'FULL' GROUP BY object_name,object_owner ORDER BY 3 DESC

OBJECT_OWNER	OBJECT_NAME	FTS_NO
User1	T1	931
User1	T2	288

2)  Below query helps to find sql id and objects name along owner which has accessed using FTS in last one day

SELECT DISTINCT

    sql_id stid,

     object_owner owner,

     object_name NAME

   FROM

 dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'TABLE ACCESS'

      AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND

      options = 'FULL' ORDER BY 1

STID	OWNER	NAME
00hk65r3g82u4	User1	T1
00wh32sp1z8j8	User1	T2

3) Using above SQL_ID you can find the sql text.

   SELECT sql_text FROM v$sqltext WHERE sql_id='0601k3shzwrdj' ORDER BY piece

4) By combining the above queries you can find the tables accesed by FTS and related sql statements

SELECT sql_id,sql_text FROM v$sqltext WHERE sql_id IN (      SELECT DISTINCT

    sql_id

   FROM

 dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'TABLE ACCESS'

      AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND

      options = 'FULL'  ) ORDER BY 1,piece