ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle的不一致性,未commit数据变成实际数据并被其他回话查询

Oracle的不一致性,未commit数据变成实际数据并被其他回话查询

原创 Oracle 作者：PiscesCanon 时间：2017-07-19 12:08:09 0 删除 编辑

操作系统版本：

  1. [ oracle @ oracle zkm ] $ uname - a
  2. Linux oracle . example . com 2 . 6 . 32 - 431 . el6 . x86_64 #1 SMP Sun Nov 10 22 : 19 : 54 EST 2013 x86_64 x86_64 x86_64 GNU / Linux
  3. [ oracle @ oracle zkm ] $ lsb_release - a
  4. LSB Version :      : base - 4 . 0 - amd64 : base - 4 . 0 - noarch : core - 4 . 0 - amd64 : core - 4 . 0 - noarch : graphics - 4 . 0 - amd64 : graphics - 4 . 0 - noarch : printing - 4 . 0 - amd64 : printing - 4 . 0 - noarch
  5. Distributor ID : RedHatEnterpriseServer
  6. Description :     Red Hat Enterprise Linux Server release 6 . 5 ( Santiago )
  7. Release :         6 . 5
  8. Codename :        Santiago


数据库版本：

  1. SYS @ proc > select * from v$version where rownum = 1 ;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11 . 2 . 0 . 4 . 0 - 64bit Production


环境构造：

  1. SYS @ proc > drop table tt purge ;

  3. Table dropped .

  5. SYS @ proc >  create table tt (id int,name varchar2(2000)) tablespace users;

  7. Table created.

  9. SYS @ proc > begin
  10.                for i in 1 . . 10000 loop
  11.                     insert into tt values ( i , rpad ( 'a' , 2000 , '+' ) ) ;
  12.                 end loop ;
  13.                commit ;
  14.           end ;
  15.           /

  17. PL / SQL procedure successfully completed .


实验过程：

  1. SYS @ proc > begin
  2.               for i in 1 . . 10000 loop
  3.                   update tt set name = rpad ( 'b' , 2000 , '-' ) where id = i ;
  4.               end loop ;
  5.           end ;
  6.           /

  8. PL / SQL procedure successfully completed .

  10. SYS @ proc >  shutd abort ;
  11. ORACLE instance shut down .
  12. SYS @ proc > exit
  13. Disconnected from Oracle Database 11g Enterprise Edition Release 11 . 2 . 0 . 4 . 0 - 64bit Production
  14. With the Partitioning , OLAP , Data Mining and Real Application Testing options
  15. [ oracle @ oracle ~ ] $ ll un *
  16. - rw - r -----. 1 oracle oinstall 104865792 Jul 19 11:19 undotbs1.dbf
  17. [ oracle @ oracle ~ ] $ rm - fr undotbs1 . dbf
  18. [ oracle @ oracle ~ ] $ sqlplus / as sysdba

  20. SQL * Plus : Release 11 . 2 . 0 . 4 . 0 Production on Wed Jul 19 11 : 20 : 17 2017

  22. Copyright ( c ) 1982 , 2013 , Oracle . All rights reserved .

  24. Connected to an idle instance .

  26. SYS @ proc > startup ;
  27. ORACLE instance started .

  29. Total System Global Area  521936896 bytes
  30. Fixed Size                    2254824 bytes
  31. Variable Size             306186264 bytes
  32. Database Buffers          209715200 bytes
  33. Redo Buffers                 3780608 bytes
  34. Database mounted .
  35. ORA - 01157 : cannot identify / lock data file 3 - see DBWR trace file
  36. ORA - 01110 : data file 3 : '/home/oracle/undotbs1.dbf'


  39. SYS @ proc > alter database datafile 3 offline drop ;

  41. Database altered .

  43. SYS @ proc > alter database open ;

  45. Database altered .

  47. SYS @ proc > create undo tablespace undotbs2 datafile '/home/oracle/undotbs2.dbf' size 100m autoextend on ;

  49. Tablespace created .

  51. SYS @ proc > alter system set undo_tablespace = undotbs2 ;

  53. System altered .

  55. SYS @ proc > show parameter undo_tablespace

  57. NAME                 TYPE      VALUE
  58. ------------------------------------ ----------- ------------------------------
  59. undo_tablespace              string     UNDOTBS2
  60. SYS @ proc > select count ( * ) from tt ;
  61. select count ( * ) from tt
  62.                       *
  63. ERROR at line 1 :
  64. ORA - 00376 : file 3 cannot be read at this time
  65. ORA - 01110 : data file 3 : '/home/oracle/undotbs1.dbf'


  68. SYS @ proc > drop tablespace undotbs1 including contents and datafiles ;
  69. drop tablespace undotbs1 including contents and datafiles
  70. *
  71. ERROR at line 1 :
  72. ORA - 01548 : active rollback segment '_SYSSMU7_1076253125$' found , terminate dropping tablespace


  75. SYS @ proc > --查询回滚段信息
  76.     select rownum ,
  77.         sys . dba_rollback_segs . segment_name Name ,
  78.         v$rollstat . extents Extents ,
  79.         v$rollstat . rssize Size_in_Bytes ,
  80.         v$rollstat . xacts XActs ,
  81.         v$rollstat . gets Gets ,
  82.         v$rollstat . waits Waits ,
  83.         v$rollstat . writes Writes ,
  84.         sys . dba_rollback_segs . status status
  85.     from v$rollstat , sys . dba_rollback_segs , v$rollname
  86.    where v$rollname . name ( + ) = sys . dba_rollback_segs . segment_name
  87.      and v$rollstat . usn ( + ) = v$rollname . usn
  88.    order by row num ;

  90.      ROWNUM NAME                                  EXTENTS SIZE_IN_BYTES      XACTS        GETS          WAITS     WRITES STATUS
  91. ---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
  92.          1 SYSTEM                                   6           385024            0           293          0      39796 ONLINE
  93.          2 _SYSSMU17_2390902301$                    2           122880            0               2           0              0 ONLINE
  94.          3 _SYSSMU18_1669837494$                    2           122880            0               2          0              0 ONLINE
  95.          4 _SYSSMU39_929646464$                      2           122880            0               4          0          138 ONLINE
  96.          5 _SYSSMU40_3450336478$                     2           122880            0          4          0         430 ONLINE
  97.          6 _SYSSMU41_3787558531$                    2           122880             0               4           0          232 ONLINE
  98.          7 _SYSSMU42_2640506269$                    2           122880            0                4          0          214 ONLINE
  99.          8 _SYSSMU43_391677854$                     2           122880             0          4          0          158 ONLINE
  100.          9 _SYSSMU44_2493446996$                    2           122880            0                7          0          900 ONLINE
  101.         10 _SYSSMU45_2356950286$                    2           122880            0                4          0        3482 ONLINE
  102.         11 _SYSSMU46_275930757$                      2           122880            0                4          0          114 ONLINE

  104.      ROWNUM NAME                              EXTENTS    SIZE_IN_BYTES      XACTS         GETS          WAITS     WRITES STATUS
  105. ---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
  106.         12 _SYSSMU12_1668912051$                                                                                    NEEDS RECOVERY
  107.         13 _SYSSMU7_1076253125$                                                                                      NEEDS RECOVERY
  108.         14 _SYSSMU16_2690255195$                                                                                    NEEDS RECOVERY
  109.         15 _SYSSMU11_3130810267$                                                                                    NEEDS RECOVERY
  110.         16 _SYSSMU13_492329380$                                                                                      NEEDS RECOVERY
  111.         17 _SYSSMU9_2756025465$                                                                                     NEEDS RECOVERY
  112.         18 _SYSSMU15_3752757294$                                                                                     NEEDS RECOVERY
  113.         19 _SYSSMU10_146814648$                                                                                     NEEDS RECOVERY
  114.         20 _SYSSMU8_4280284895$                                                                                      NEEDS RECOVERY
  115.         21 _SYSSMU14_3551377741$                                                                                     NEEDS RECOVERY

  117. 21 rows selected .

  119. SYS @ proc > shutdown immediate ;
  120. Database closed .
  121. Database dismounted .
  122. ORACLE instance shut down .
  123. SYS @ proc > create pfile from spfile ;

  125. File created .


  128. [ oracle @ oracle dbs ] $ cat initproc . ora | grep offline_rollback_segments
  129. * . _offline_rollback_segments = '_SYSSMU12_1668912051$' , '_SYSSMU7_1076253125$' , '_SYSSMU16_2690255195$' , '_SYSSMU11_3130810267$' , '_SYSSMU13_492329380$' , '_SYSSMU9_2756025465$' , '_SYSSMU15_3752757294$' , '_SYSSMU10_146814648$' , '_SYSSMU8_4280284895$' , '_SYSSMU14_3551377741$'

  131. SYS @ proc > create spfile from pfile ;

  133. File created .

  135. SYS @ proc > startup ;
  136. ORACLE instance started .

  138. Total System Global Area  521936896 bytes
  139. Fixed Size                     2254824 bytes
  140. Variable Size              306186264 bytes
  141. Database Buffers          209715200 bytes
  142. Redo Buffers                 3780608 bytes
  143. Database mounted .
  144. Database opened .
  145. SYS @ proc > show parameter offline_roll

  147. NAME                                 TYPE         VALUE
  148. ------------------------------------ ----------- ------------------------------
  149. _offline_rollback_segments              string     _SYSSMU12_1668912051$ , _SYSSMU
  150.                                                 7_1076253125$ , _SYSSMU16_26902
  151.                                                 55195$ , _SYSSMU11_3130810267$ ,
  152.                                                  _SYSSMU13_492329380$ , _SYSSMU
  153.                                                 9_2756025465$ , _SYSSMU15_37527
  154.                                                 57294$ , _SYSSMU10_146814648$ ,
  155.                                                 _SYSSMU8_4280284895$ , _SYSSMU1
  156.                                                 4_3551377741$
  157. SYS @ proc > select count ( * ) from tt ;
  158. select count ( * ) from tt
  159.                       *
  160. ERROR at line 1 :
  161. ORA - 00376 : file 3 cannot be read at this time
  162. ORA - 01110 : data file 3 : '/home/oracle/undotbs1.dbf'

  164. SYS @ proc > drop tablespace undotbs1 including contents and datafiles ;

  166. Tablespace dropped .

  168. SYS @ proc > select count ( * ) from tt ;

  170.    COUNT ( * )
  171. ----------
  172.      10000

  174. SYS @ proc >
  1. SYS @ proc > alter system reset "_offline_rollback_segments" ;

  3. System altered .

  5. SYS @ proc > startup force ;
  6. ORACLE instance started .

  8. Total System Global Area  521936896 bytes
  9. Fixed Size                    2254824 bytes
  10. Variable Size             306186264 bytes
  11. Database Buffers         209715200 bytes
  12. Redo Buffers                 3780608 bytes
  13. Database mounted .
  14. Database opened .
  15. SYS @ proc > show parameter _offline_rollback_segments
  16. SYS @ proc >


查询结果：
select * from tt;(在PLSQL Developer 12 (64 bit)工具中执行)


总结：
需要注意的是，不是每次结果都能看到有a和b结果的混合，很大几率只能看到都是b---..的情况，不过b--..都是未commit的数据，已经达到目的。
至于为什么会这样，这涉及到oracle实例恢复原理，读一致性原理，ITL等等知识，这里就不详述了。




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

0
0
上一篇： Oracle中数据块中row number缺失(记录，未解决)
下一篇： Oracle脚本收集【不定时更新】
请登录后发表评论 登录
全部评论
PiscesCanon

注册时间：2015-03-23

  • 博文量
    29
  • 访问量
    57488

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593