oracle 9i闪回schema,Oracle 9i闪回查询的新特性

从Oracle9i开始，Oracle开始提供闪回查询特性(flashback query)，允许将回滚段中的数据进行闪回。通过这个例子我们来看一下这个从Oracle9i开始提供的新特性。

首先我们注意到这里存在一个信息: ctl max scn: 0x0819.003f594b ，这个转换为SCN值就是：SQL> select (to_number('819','xxxx')*power(2,32) + to_number('3f594b','xxxxxxxx')) scn

2  from dual;

SCN

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

8903471356235

可以查询一下当前数据库的SCN：SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN

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

8903471437610

通过特定的语法，我们可以将SCN为8903471356235的历史状态数据查询出来：SQL> select * from emp as of scn 8903471356235 where empno in (7788,7782,7698);

EMPNO ENAME      JOB                 MGR HIREDATE         SAL       COMM    DEPTNO

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

7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

在结果中，我们注意到3名员工的薪水恢复到了之前值。而在当前的查询中，这个数值是变化后的4000：SQL> select * from emp where empno in (7788,7782,7698);

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7698 BLAKE      MANAGER         7839 01-MAY-81       4000                    30

7782 CLARK      MANAGER         7839 09-JUN-81       4000                    10

7788 SCOTT      ANALYST          7566 19-APR-87       4000                    20

由于这个查询需要从UNDO中获取前镜像信息，如果UNDO中的信息被覆盖，则以上查询将会失败。为了模拟不同情况，创建一个新的UNDO表空间，切换数据库使用新的 UNDO表空间，再将原表空间Offline：SQL> create undo tablespace undotbs datafile '/opt/oracle/oradata/conner/undotbs.dbf' size 2M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs;

System altered.

SQL> alter tablespace UNDOTBS1 offline;

Tablespace altered.

SQL> alter session set events = 'immediate trace name flush_cache';

Session altered.

再来查询，此时出现错误，记录该文件已经不可读取：SQL> select * from emp as of scn 8903471356235 where empno in (7788,7782,7698);

select * from emp as of scn 8903471356235 where empno in (7788,7782,7698)

*

ERROR at line 1:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/opt/oracle/oradata/conner/undotbs1.dbf'

将UNDOTBS1重新启用，则此时前镜像信息再次可以查询：SQL> alter tablespace UNDOTBS1 online;

Tablespace altered.

SQL> alter system set undo_tablespace=UNDOTBS1;

System altered.

在其他Session执行大量事务，使得前镜像信息被覆盖：SQL> begin

2  for i in 1 .. 2000 loop

3  update emp set sal=4000;

4  rollback;

5  end loop;

6  end;

7  /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> /

观察回滚段的使用：SQL> select usn,xacts,RSSIZE,HWMSIZE from v$rollstat where usn=6;

USN      XACTS     RSSIZE    HWMSIZE

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

6          １    7331840    7331840

那么再次查询就可能收到如下错误：SQL> select * from emp as of scn 8903471356235 where empno in (7788,7782,7698);

select * from emp as of scn 8903471356235 where empno in (7788,7782,7698)

*

ERROR at line 1:

ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small

ORA-01555错误出现，说明要查询的前镜像信息已经失去。