Goal is to fetch the query's which are using 'ABC' table before we drop 'ABC' Table. Analysis is required to know when was table last used and what queries are hitting on ' ABC' table before we plan to drop it.
I wrote this below query which is functional but seems that it is not accurate.
In output result : I got the list of sql statements but it include the irrelevant data as it showed insert statement hitting on 'xyz' table. Whereas I was hoping to get result of only 'ABC' table.v.S tableQL_TEXT, v.SQL_ID,
I would appreciate if experts can suggest what I am doing wrong and how I can get the data which I am looking for ?
Select DISTINCT v.SQL_TEXT, v.SQL_ID, o.owner,o.object_name, o.object_type,v.LAST_ACTIVE_TIME
from dba_hist_sqlstat t, dba_hist_snapshot s, dba_objects o, v$sqlarea v
where t.snap_id = s.snap_id
and t.dbid = s.dbid
and t.SQL_ID= v.SQL_ID
and t.instance_number = s.instance_number
and s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate)
and o.OBJECT_TYPE='TABLE'
and o.OBJECT_NAME='ABC'
/
Thanks in Advance.