oracle9i利用UNDO闪回，前提是被删除的数据在UNDO中没有被覆盖

最新推荐文章于 2021-04-13 13:44:32 发布

zhaojian1988

最新推荐文章于 2021-04-13 13:44:32 发布

阅读量536

点赞数

分类专栏： oracle 基础 ora-XXX

本文链接：https://blog.csdn.net/zhaojian1988/article/details/42122491

版权

oracle 基础同时被 2 个专栏收录

93 篇文章 0 订阅

订阅专栏

ora-XXX

29 篇文章 0 订阅

订阅专栏

利用UNDO闪回，前提是被删除的数据在UNDO中没有被覆盖。

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1080
undo_tablespace string UNDOTBS1

10:29:08 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SCN from dual;

TO_CHAR(SYSDATE,'YY SCN

-----------------------------------------------------------

2011-03-29 10:29:28 11029032818473

10:29:28 SQL> select count(*) from JR_CARD;

COUNT(*)

----------

10:31:04 SQL> SELECTcount(*) FROM JR_CARD AS OF TIMESTAMP TO_TIMESTAMP('2011-03-2910:26:59','YYYY-MM-DD HH:MI:SS');

COUNT(*)

----------

6758

10:31:43 SQL> insertinto JR_CARD SELECT * FROM JR_CARD AS OF TIMESTAMP TO_TIMESTAMP('2011-03-2910:26:59','YYYY-MM-DD HH:MI:SS');

已创建6758行。

10:32:17 SQL> select count(*) from JR_CARD;

COUNT(*)

----------

6758

10:32:25 SQL> commit;

提交完成。

10:32:27 SQL> select count(*) from JR_CARD;

COUNT(*)

----------

6758

10:32:34 SQL>

此方法恢复只需1分钟即可。

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SCN from dual;

SELECT count(*) FROM T6 AS OF TIMESTAMP TO_TIMESTAMP('2014-12-24 12:34:19','YYYY-MM-DD HH:MI:SS');

create table T6_bak as SELECT * FROM T6 AS OF TIMESTAMP TO_TIMESTAMP('2014-12-24 12:33:25','YYYY-MM-DD HH:MI:SS');