Oracle 进行表数据恢复（转）

来源：互联网发布：android 网络请求原理编辑：程序博客网时间：2024/05/10 04:49

本文转载自：
http://www.cnblogs.com/Automation_software/archive/2011/03/11/1981370.html

1、建表-- Create tablecreate table DARCY(ID   NUMBER,INFO NVARCHAR2(32))tablespace DATA_SGPMpctfree 10initrans 1maxtrans 255storage(    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 cccSQL> delete from darcy where id = 1;1 row deletedSQL> commit;Commit completeSQL> 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 alteredSQL> flashback table DARCY to timestamp to_timestamp(2010-9-9 10:17:1,'yyyy-mm-dd hh24:mi:ss');DoneSQL> SELECT * FROM DARCY;        ID INFO---------- --------------------------------------------------------------------------------         1 aaa         2 bbb         3 ccc

5、drop表后的恢复

SQL> drop table darcy;Table droppedSQL> select * from darcy;select * from darcyORA-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_SGPMSQL> 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          8SQL> flashback table darcy to before drop;DoneSQL> select * from darcy;        ID INFO---------- --------------------------------------------------------------------------------         1 aaa         2 bbb         3 ccc

0 0