資料庫中有時候會執行一些很大的事務，這些事務會持續幾個小時；如果這些事務在執行過程中被人為殺掉，資料庫會正常回滾嗎？

在本機做了一些關於回滾段的測試，來驗證這個問題？

檢視現有 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 不夠，語句會失敗，反之，則開始事務。

oracle 11g 回滾段的測試

相關文章