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;

select * from darcy

ORA-00942: 表或视图不存在

SQL> select * from 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

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