|
本帖最后由 蝴蝶 于 2015-1-7 17:27 编辑
oracle数据库操作不小心把数据表内的数据删除了,这可是一件非常不幸的消息,于是会 ---》杯具了,残酷的事实摆在面前,如何恢复数据变得十分严峻,下面是模拟oracle数据库删除表内数据进行恢复。
1、建表
- -- Create table
- create table DARCY
- (
- ID NUMBER,
- INFO NVARCHAR2(32)
- )
- tablespace DATA_SGPM
- pctfree 10
- initrans 1
- maxtrans 255
- storage
- (
- initial 64K
- minextents 1
- maxextents unlimited
- );
复制代码
2、插入数据
- insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');
- insert into "SGPM"."DARCY"("ID","INFO") values ('2','bbb');
- insert into "SGPM"."DARCY"("ID","INFO") values ('3','ccc');
复制代码
3、删除数据
- SQL> select * from darcy;
- ID INFO
- ---------- --------------------------------------------------------------------------------
- 1 aaa
- 2 bbb
- 3 ccc
- SQL> delete from darcy where id = 1;
- 1 row deleted
- SQL> commit;
- Commit complete
- SQL> select * from darcy;
- ID INFO
- ---------- --------------------------------------------------------------------------------
- 2 bbb
- 3 ccc
复制代码
4、恢复数据
方法1:
查询最新的系统变更number
- SQL> select dbms_flashback.get_system_change_number from dual;
- GET_SYSTEM_CHANGE_NUMBER
- ------------------------
- 18144344
复制代码
查看此次变更后的表记录
- SQL> select * from darcy as of scn 18144344;
复制代码
ID INFO
---------- --------------------------------------------------------------------------------
2 bbb
3 ccc
说明这是删除数据后的表记录,我们只要找到某个scn,即删除表记录前的scn,
恢复到这个scn时的记录。
- SQL> SELECT * FROM DARCY as of scn 18144252;
复制代码
ID INFO
---------- --------------------------------------------------------------------------------
1 aaa
2 bbb
3 ccc
然后直接执行insert语句
方法2:
- SQL> select * from flashback_transaction_query where table_name='DARCY';
复制代码
XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL
---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
07001500AB280000 18144149 2010-9-9 10:14: 18144281 2010-9-9 10:17:1 SGPM 1 DELETE DARCY SGPM AAAYQwAAcAAAKk2AAA insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');
080018005F370000 18144244 2010-9-9 10:16: 18144252 2010-9-9 10:16:3 SGPM 1 INSERT DARCY SGPM AAAYQwAAcAAAKk2AAC delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAC';
080018005F370000 18144244 2010-9-9 10:16: 18144252 2010-9-9 10:16:3 SGPM 2 INSERT DARCY SGPM AAAYQwAAcAAAKk2AAB delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAB';
080018005F370000 18144244 2010-9-9 10:16: 18144252 2010-9-9 10:16:3 SGPM 3 INSERT DARCY SGPM AAAYQwAAcAAAKk2AAA delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAA';
执行UNDO_SQL,即:insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');即可恢复数据。
或者直接运行:
- SQL> flashback table DARCY to timestamp to_timestamp('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss');
复制代码
flashback table DARCY to timestamp to_timestamp('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss')
ORA-08189: 因为未启用行移动功能, 不能闪回表
- SQL> alter table DARCY enable row movement;
复制代码
Table altered
- SQL> flashback table DARCY to timestamp to_timestamp(2010-9-9 10:17:1,'yyyy-mm-dd hh24:mi:ss');
- Done
- SQL> SELECT * FROM DARCY;
复制代码
ID INFO
---------- --------------------------------------------------------------------------------
1 aaa
2 bbb
3 ccc
5、drop表后的恢复
- SQL> drop table darcy;
- Table dropped
- SQL> select * from darcy;
复制代码
ORA-00942: 表或视图不存在
- SQL> select * from recyclebin;[/color]
- OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
- ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
- BIN$CrbfFp0nRTWzETrAMvbD+A==$0 DARCY DROP TABLE DATA_SGPM
- [code]SQL> SELECT * FROM USER_RECYCLEBIN;
复制代码
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$CrbfFp0nRTWzETrAMvbD+A==$0 DARCY DROP TABLE DATA_SGPM 2010-09-09:10:15:50 2010-09-09:11:12:04 18154031 YES YES 99376 99376 99376 8
- SQL> flashback table darcy to before drop;
- Done
- SQL> select * from darcy;
复制代码
ID INFO
---------- --------------------------------------------------------------------------------
1 aaa
2 bbb
3 ccc
INTOHARD.COM
|
|