oracle表数据恢复1

转载 2012年03月31日 11:58:06

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

此处注意闪回时间点的选取,如时间点在表建立之前会提示ORA-01466:无法读取数据的错误;

如在闪回前对表结构进行修改也会提示此错误.

 

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 

相关文章推荐

Oracle 进行表数据恢复(转)

Oracle 表数据恢复

oracle 锁表与解锁、数据恢复

锁表与解锁 SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK...

ORACLE将表中的数据恢复到某一个时间点

概念: You perform a Flashback Query by using a SELECT statementwith an AS OF clause.You use a flashba...

oracle表数据恢复2

1.表查询闪回 create table xcp as (select * from b_za_bzdzkxx); select * from xcp; select count(1) from...

oracle表数据恢复

  • 2013-04-11 17:38
  • 149B
  • 下载

Oracle 10g 数据恢复(闪回FLAHBACK)

1、闪回数据库 Flashbackdatabase(根据误操作时间闪回数据) ·DB_RECOVER_FILE_DEST ·DB_RECOVER_FILE_DEST_SIZE 这两个参数来确...

Oracle Logminer 做数据恢复 说明示例

在IBM DBA 小荷的blog上看到一个用Logminer 做数据恢复的例子。 虽然对Logminer 也了解一点,但是用Logminer 做恢复还真没用过,所以也测试一下。 原文链接地址如下:  ...

Oracle数据恢复工具-ODU破解记录

Oracle数据恢复工具-ODU破解 ODU全称为Oracle Database Unloader,是由OracleODU开发的类似于Oracle的DUL(Oracle内部著名的数据库恢复工具)的一...
  • zhw309
  • zhw309
  • 2013-05-09 10:47
  • 4924

Oracle数据恢复--flashback

flash back 对表的恢复,对数据的恢复

oracle数据库delete 后数据恢复

oracle数据库delete后数据删除后恢复,需要依靠scn的编号来恢复,但是恢复时候需要找到之前scn活动的,才能恢复;
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)