从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 1 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错误出现,说明要查询的前镜像信息已经失去。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。