9i 基本时间flashback的版本查询

最新推荐文章于 2025-04-25 18:07:04 发布

cka6619

最新推荐文章于 2025-04-25 18:07:04 发布

阅读量149

点赞数

文章标签：数据库

本文详细介绍了在Oracle9iR2中如何利用SMON_SCN_TIME进行数据闪回查询，包括不同时间点的数据恢复过程及注意事项，适用于不慎删除数据后的恢复场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--9i 基本时间flashback的版本查询：

在Oracle9iR2中,SMON_SCN_TIME每5分钟被更新一次,在Oracle10g中, 在SMON_SCN_TIME表每6秒被更新一次.
要查之前表的版本，一定要在 SMON_SCN_TIME 切换过之后才能查到：

在9i里版本时间应该在undo_retention之内，并且undo 没有被副覆盖，9i可以通过这种方式来恢复在undo_retention之内删除的数据。

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

SQL> create table test_t2(id int,name varchar2(30));

Table created.

SQL> select * from (select * from smon_scn_time order by time_dp desc) where rownum <5;

    THREAD    TIME_MP TIME_DP                SCN_WRP    SCN_BAS
---------- ---------- ------------------- ---------- ----------
         1 1224495553 2008-10-20 17:39:13       2244 2255475957
         1 1224495252 2008-10-20 17:34:13       2244 2255475853
         1 1224494653 2008-10-20 17:24:13       2244 2255475638
         1 1224494351 2008-10-20 17:19:13       2244 2255475527

SQL> select sysdate from dual;

SYSDATE
-------------------
2008-10-20 17:44:07

SQL> insert into test_t2 values(99,'aaa');

1 row created.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2008-10-20 17:44:34

SQL> select * from (select * from smon_scn_time order by time_dp desc) where rownum <5;

--这时SMON_SCN_TIME还没有切换,查不到刚插入的值。
SQL> SELECT * FROM test_t2 AS OF TIMESTAMP to_timestamp('2008-10-20 17:44:34','yyyy-mm-dd hh24:mi:ss');

no rows selected

SQL> select sysdate from dual;

SYSDATE
-------------------
2008-10-20 17:47:03

SQL> SELECT * FROM test_t2 AS OF TIMESTAMP to_timestamp('2008-10-20 17:47:03','yyyy-mm-dd hh24:mi:ss');

no rows selected

SQL> select * from (select * from smon_scn_time order by time_dp desc) where rownum <5;

    THREAD    TIME_MP TIME_DP                SCN_WRP    SCN_BAS
---------- ---------- ------------------- ---------- ----------
         1 1224496151 2008-10-20 17:49:13       2244 2255476168
         1 1224495553 2008-10-20 17:39:13       2244 2255475957
         1 1224495252 2008-10-20 17:34:13       2244 2255475853
         1 1224494653 2008-10-20 17:24:13       2244 2255475638

--查在SMON_SCN_TIME切换的前一秒，数据还是不存在：
SQL> SELECT * FROM test_t2 AS OF TIMESTAMP to_timestamp('2008-10-20 17:49:12','yyyy-mm-dd hh24:mi:ss');

no rows selected

--一定要在 SMON_SCN_TIME 切换过之后才能查到：
SQL> SELECT * FROM test_t2 AS OF TIMESTAMP to_timestamp('2008-10-20 17:49:13','yyyy-mm-dd hh24:mi:ss');

ID NAME
---------- ------------------------------
99 aaa

当我们不小心删除了数据时，可以先创建一张临时表，将丢失的数据找回来。

在2008-10-20 17:50:13做了

delete * from test_t2 where id=99;
commit;

SQL> select * from (select * from smon_scn_time order by time_dp desc) where rownum <5;

    THREAD    TIME_MP TIME_DP                SCN_WRP    SCN_BAS
---------- ---------- ------------------- ---------- ----------
         1 1224496153 2008-10-20 17:54:13       2244 2255477156
         1 1224496151 2008-10-20 17:49:13       2244 2255476168
         1 1224495553 2008-10-20 17:39:13       2244 2255475957
         1 1224495252 2008-10-20 17:34:13       2244 2255475853
         1 1224494653 2008-10-20 17:24:13       2244 2255475638

create table tmp as SELECT * FROM test_t2 AS OF TIMESTAMP to_timestamp('2008-10-20 17:54:13','yyyy-mm-dd hh24:mi:ss');

insert into test_t2 as select * from tmp where id=99;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10834762/viewspace-474643/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10834762/viewspace-474643/