老麻
前几天生产环境oracle 数据库一张表忽然数据全部被伪删除了，找资料找到以下两种方式追溯update 的SQL：
1、
select username,program,sql_text,antma,command_type,sample_time
from (
select c.username,a.program,b.sql_text,dbms_lob.substr(b.sql_text,4000,1) antma,b.command_type,a.sample_time
from dba_hist_active_sess_history a join dba_hist_sqltext b on a.sql_id = b.sql_id
join dba_users c on a.user_id = c.user_id
where a.sample_time between sysdate – 5 and sysdate –and b.command_type in(6)
order by a.sample_time desc
) A where UPPER(antma) like ‘UPDATE SCHEMA.TABLE%‘
;
其中”UPDATE
SCHEMA.TABLE%“ 是因为我要找这张表，所以这样过滤一下；
”5“表示要往前追溯几天，这个是有
”6“表示要找”UPDATE“类型的
2.
select * from v$sql
where parsing_schema_name = ‘SCHEMA‘
and module = ‘JDBC Thin Client‘
and service=’hbres’
and UPPER(sql_text) like ‘UPDATE SCHEMA.TABLE%‘
order by last_load_time ;
