SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
误删数据后,立刻发现,采用方式:
根据时间恢复:
select count(1) from test; --0
insert into test
select * from test as of timestamp to_timestamp('2015-01-22 15:14:00','yyyy-mm-dd hh24:mi:ss');
select count(1) from test; --5070
根据SCN恢复:
获取当前SCN号:select dbms_flashback.get_system_change_number fscn from dual;
delete from test;
commit;
select count(1) from test; --0
insert into test
select * from test as of scn 11431740649396;
select count(1) from test; --5070
以上方式恢复数据,需要时间在undo_retention之内,且,undo未被重用,如果不存在,且有备份,可以在测试库将备份恢复出来。
SCN与timestamp之间的转换:
select scn_to_timestamp(11431740649396) from dual;
select timestamp_to_scn(to_timestamp('2015-01-22 15:14:25','yyyy-mm-dd hh24:mi:ss')) from dual;
数据块未被覆盖也可以使用bbed恢复(少量数据),慎用(还没看明白)。
表被drop:
SQL> drop table test;
Table dropped
SQL> select count(1) from test;
select count(1) from test
ORA-00942: 表或视图不存在
--查看回收站信息
SQL> select * from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME
BIN$DTmXjQWbF3rgUKjAHwA3Gg==$0 TEST DROP TABLE RTMS_TS 2015-01-20:15:18:20
闪回表:
SQL> flashback table test to before drop;
Done
SQL> select count(1) from test;
COUNT(1)
----------
5070
SQL> drop table test;
Table dropped
--闪回表并重命名
SQL> flashback table test to before drop rename to test_1;
Done
SQL> select count(1) from test;
select count(1) from test
ORA-00942: 表或视图不存在
SQL> select count(1) from test_1;
COUNT(1)
----------
5070
如果删除时间过程但有备份且开启归档:可以在测试库中根据scn或时间点还原数据,然后将还原后的数据导入到生产中。