FIND FULL TABLE SCAN IN DATABASE

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