oracle回滚段数据结构,oracle 11g 回滚段的测试

数据库中有时候会运行一些很大的事务，这些事务会持续几个小时；如果这些事务在运行过程中被人为杀掉，数据库会正常回滚吗？

在本机做了一些关于回滚段的测试，来验证这个问题？

查看现有 UNDO 表空间

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

现有 UNDO 表空间为100MB

SQL>select BYTES/1024/1024 "Mb" from dba_data_files where tablespace_name='UNDOTBS1';

Mb

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

100

创建一个新的 UNDO 表空间 UNDOTBS2 ，大小为 100 KB

SQL>CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\FIRE\UNDOTBS02.DBF' SIZE 100K REUSE AUTOEXTEND OFF;

更改 UNDO 表空间为 UNDOTBS2

SQL> create pfile from spfile;

编辑 pfile，将 UNDO 表空间改为 UNDOTBS2

*.undo_tablespace='UNDOTBS2'

重启数据库，让参数生效

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

在数据库的 SCOTT 用户下，有一张 30000 多条数据的条，尝试删除里面的数据

C:\Users\Administrator>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 25 16:27:41 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from test;

COUNT(*)

----------

37194

SQL> desc test

名称                                                                       是否为空? 类型

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

A                                                                                   VARCHAR2(35)

B                                                                                   DATE

EMP_NO                                                                              NUMBER(32)

DEPT_NO                                                                             NUMBER(32)

SQL> select * from test where rownum <= 10;

A                                   B                  EMP_NO    DEPT_NO

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

2015/10/1 0:33:01                   01-10月-15

2015/10/1 0:33:16                   01-10月-15

2015/10/1 0:33:29                   01-10月-15

2015/10/1 0:35:43                   01-10月-15

2015/10/1 0:38:29                   01-10月-15

2015/10/1 0:38:38                   01-10月-15

2015/10/1 0:38:56                   01-10月-15

2015/10/1 0:39:57                   01-10月-15

2015/10/1 0:40:09                   01-10月-15

2015/10/1 0:42:43                   01-10月-15

已选择10行。

SQL> delete from test where rownum=1;

delete from test where rownum=1

*

第 1 行出现错误:

ORA-01552: 非系统表空间 'USERS' 不能使用系统回退段

删除报错，扩展 UNDO 表空间之后，再重新执行上面删除命令

SQL>alter database datafile 'E:\APP\ADMINISTRATOR\ORADATA\FIRE\UNDOTBS02.DBF' resize 200k;

SQL> delete from test where rownum=1;

已删除 1 行。

尝试删除表中的所有数据

SQL> delete from test;

delete from test

*

第 1 行出现错误:

ORA-30036: 无法按 8 扩展段 (在还原表空间 'UNDOTBS2' 中)

回滚表空间没有设置自动增长，手动扩展回滚表空间

SQL>alter database datafile 'E:\APP\ADMINISTRATOR\ORADATA\FIRE\UNDOTBS02.DBF' resize 200k;

删除 100 行数据成功

SQL> delete from test where rownum <= 100;

已删除100行。

SQL> rollback;

回退已完成。

尝试为表空间增加一个新的字段，字段带有默认值

SQL> alter table test add sale_id number(32) default 10;

alter table test add sale_id number(32) default 10

*

第 1 行出现错误:

ORA-30036: 无法按 8 扩展段 (在还原表空间 'UNDOTBS2' 中)

将 UNDO 表空间增大到 10M，并创建一张将近 30w 条数据的表，并尝试在表上增加一个带有非空默认值的字段，在增加字段进行的过程中，在事务结束前，将这个事务杀掉，来看 oracle 数据库能否回退事务

SQL>alter database datafile 'E:\APP\ADMINISTRATOR\ORADATA\FIRE\UNDOTBS02.DBF' resize 10M;

SQL> create table test2 as select * from dba_objects;

表已创建。

SQL> insert into test2 select * from test2;

已创建73237行。

SQL> commit;

提交完成。

SQL> insert into test2 select * from test2;

已创建146474行。

SQL> commit;

提交完成。

SQL> select count(*) from test2;

COUNT(*)

----------

292948

在增加字段执行的过程中，将此会话杀掉

SQL> select * from v$mystat where rownum = 1;

SID STATISTIC#      VALUE

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

191          0          0

SQL> alter table test2 add empno number(32) default 100;

alter table test2 add empno number(32) default 100

*

第 1 行出现错误:

ORA-00028: your session has been killed

ORA-00028: your session has been killed

打开新的会话，可以发现增加表字段没有成功，数据库将表回退到增加之前的状态

C:\Users\Administrator>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 25 15:35:09 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc test2

名称                                                                       是否为空? 类型

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

OWNER                                                                               VARCHAR2(30)

OBJECT_NAME                                                                         VARCHAR2(128)

SUBOBJECT_NAME                                                                      VARCHAR2(30)

OBJECT_ID                                                                           NUMBER

DATA_OBJECT_ID                                                                      NUMBER

OBJECT_TYPE                                                                         VARCHAR2(19)

CREATED                                                                             DATE

LAST_DDL_TIME                                                                       DATE

TIMESTAMP                                                                           VARCHAR2(19)

STATUS                                                                              VARCHAR2(7)

TEMPORARY                                                                           VARCHAR2(1)

GENERATED                                                                           VARCHAR2(1)

SECONDARY                                                                           VARCHAR2(1)

NAMESPACE                                                                           NUMBER

EDITION_NAME                                                                        VARCHAR2(30)

通过上面的实验可以知道，Oracle 数据库在开始执行一个 DML 操作的时候，会先检查 UNDO 表空间的回滚段是否能够满足此次事务的需求，如果回滚段中的 BLOCK 不够，语句会失败，反之，则开始事务。

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