【大胆的实验】Oracle的不一致性,未commit数据变成实际数据并被其他回话查询-优快云博客

操作系统版本：

[oracle@oracle zkm]$ uname -a
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
[oracle@oracle zkm]$ lsb_release -a
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
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago

数据库版本：

SYS@proc> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

环境构造：

SYS@proc> drop table tt purge;
Table dropped.
SYS@proc> create table tt (id int,name varchar2(2000)) tablespace users;
Table created.
SYS@proc> begin
for i in 1..10000 loop
insert into tt values(i,rpad('a',2000,'+'));
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.

实验过程：

SYS@proc> begin
for i in 1..10000 loop
update tt set name=rpad('b',2000,'-') where id=i;
end loop;
end;
/
PL/SQL procedure successfully completed.
SYS@proc> shutd abort;
ORACLE instance shut down.
SYS@proc> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle ~]$ ll un*
-rw-r-----. 1 oracle oinstall 104865792 Jul 19 11:19 undotbs1.dbf
[oracle@oracle ~]$ rm -fr undotbs1.dbf
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 19 11:20:17 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@proc> startup;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 306186264 bytes
Database Buffers 209715200 bytes
Redo Buffers 3780608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/undotbs1.dbf'
SYS@proc> alter database datafile 3 offline drop;
Database altered.
SYS@proc> alter database open;
Database altered.
SYS@proc> create undo tablespace undotbs2 datafile '/home/oracle/undotbs2.dbf' size 100m autoextend on;
Tablespace created.
SYS@proc> alter system set undo_tablespace=undotbs2;
System altered.
SYS@proc> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
SYS@proc> select count(*) from tt;
select count(*) from tt
*
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/undotbs1.dbf'
SYS@proc> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU7_1076253125$' found, terminate dropping tablespace
SYS@proc> --查询回滚段信息
select rownum,
sys.dba_rollback_segs.segment_name Name,
v$rollstat.extents Extents,
v$rollstat.rssize Size_in_Bytes,
v$rollstat.xacts XActs,
v$rollstat.gets Gets,
v$rollstat.waits Waits,
v$rollstat.writes Writes,
sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
and v$rollstat.usn(+) = v$rollname.usn
order by rownum;
ROWNUM NAME EXTENTS SIZE_IN_BYTES XACTS GETS WAITS WRITES STATUS
---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
1 SYSTEM 6 385024 0 293 0 39796 ONLINE
2 _SYSSMU17_2390902301$ 2 122880 0 2 0 0 ONLINE
3 _SYSSMU18_1669837494$ 2 122880 0 2 0 0 ONLINE
4 _SYSSMU39_929646464$ 2 122880 0 4 0 138 ONLINE
5 _SYSSMU40_3450336478$ 2 122880 0 4 0 430 ONLINE
6 _SYSSMU41_3787558531$ 2 122880 0 4 0 232 ONLINE
7 _SYSSMU42_2640506269$ 2 122880 0 4 0 214 ONLINE
8 _SYSSMU43_391677854$ 2 122880 0 4 0 158 ONLINE
9 _SYSSMU44_2493446996$ 2 122880 0 7 0 900 ONLINE
10 _SYSSMU45_2356950286$ 2 122880 0 4 0 3482 ONLINE
11 _SYSSMU46_275930757$ 2 122880 0 4 0 114 ONLINE
ROWNUM NAME EXTENTS SIZE_IN_BYTES XACTS GETS WAITS WRITES STATUS
---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
12 _SYSSMU12_1668912051$ NEEDS RECOVERY
13 _SYSSMU7_1076253125$ NEEDS RECOVERY
14 _SYSSMU16_2690255195$ NEEDS RECOVERY
15 _SYSSMU11_3130810267$ NEEDS RECOVERY
16 _SYSSMU13_492329380$ NEEDS RECOVERY
17 _SYSSMU9_2756025465$ NEEDS RECOVERY
18 _SYSSMU15_3752757294$ NEEDS RECOVERY
19 _SYSSMU10_146814648$ NEEDS RECOVERY
20 _SYSSMU8_4280284895$ NEEDS RECOVERY
21 _SYSSMU14_3551377741$ NEEDS RECOVERY
21 rows selected.
SYS@proc> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@proc> create pfile from spfile;
File created.
[oracle@oracle dbs]$ cat initproc.ora | grep offline_rollback_segments
*._offline_rollback_segments='_SYSSMU12_1668912051$','_SYSSMU7_1076253125$','_SYSSMU16_2690255195$','_SYSSMU11_3130810267$','_SYSSMU13_492329380$','_SYSSMU9_2756025465$','_SYSSMU15_3752757294$','_SYSSMU10_146814648$','_SYSSMU8_4280284895$','_SYSSMU14_3551377741$'
SYS@proc> create spfile from pfile;
File created.
SYS@proc> startup;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 306186264 bytes
Database Buffers 209715200 bytes
Redo Buffers 3780608 bytes
Database mounted.
Database opened.
SYS@proc> show parameter offline_roll
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_offline_rollback_segments string _SYSSMU12_1668912051$, _SYSSMU
7_1076253125$, _SYSSMU16_26902
55195$, _SYSSMU11_3130810267$,
_SYSSMU13_492329380$, _SYSSMU
9_2756025465$, _SYSSMU15_37527
57294$, _SYSSMU10_146814648$,
_SYSSMU8_4280284895$, _SYSSMU1
4_3551377741$
SYS@proc> select count(*) from tt;
select count(*) from tt
*
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/undotbs1.dbf'
SYS@proc> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS@proc> select count(*) from tt;
COUNT(*)
----------
10000
SYS@proc>