通过闪回查询和事物查询恢复小记

来源：互联网发布：java pow方法编辑：程序博客网时间：2024/05/01 02:40

1、

SQL> select count(*) from test order by id;

COUNT(*)
----------
120

2、delete from test where id<50

commit;

3、

SQL> select dbms_flashback.get_system_change_number from dual;----最好查讯到当前scn，否则有时会出现

ORA-30052: invalid lower limit snapshot expression错误，SQL>alter system set undo_retention=10800 scope=both;

GET_SYSTEM_CHANGE_NUMBER
------------------------
610896

4、select versions_xid, versions_operation from TEST versions between scn 610000 and 610896 order by 2;

versions_xid versions_operation

06002E0019010000 D

...................................................................

5、select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid='06002E0019010000'

6、通过scn闪回，提交的scn为610521，应该闪回到610520

insert into test
select * from test as of scn 610520
where id not in (select id from test);

7、通过时间闪回

insert into test
select * from test as of timestamp to_timestamp('2010-5-24 10:29:15', 'yyyy-mm-dd hh24:mi:ss')
where id not in (select id from test);

commit；