ORACLE 某表执行SQL历史,Java 调用历史SQL

2017/08 03 12:08


前几天生产环境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 ;