ORACLE 回滾段

回滾段概述

　　回滾段用於存放資料修改之前的值（包括資料修改之前的位置和值）。回滾段的頭部包含正在使用的該回滾段事務的資訊。一個事務只能使用一個回滾段來存放它的回滾資訊，而一個回滾段可以存放多個事務的回滾資訊。

回滾段的作用

　　 事務回滾 ：當事務修改表中資料的時候，該資料修改前的值（即前影像）會存放在回滾段中，當使用者回滾事務（ROLLBACK）時，ORACLE將會利用回滾段中的資料前影像來將修改的到原來的值。

　　事務恢復 ：當事務正在處理的時候，例程失敗，回滾段的資訊儲存在重做日誌檔案中，ORACLE將在下次開啟資料庫時利用回滾來恢復未提交的資料。

　　 讀一致性 ：當一個會話正在修改資料時，其他的會話將看不到該會話未提交的修改。而且，當一個語句正在執行時，該語句將看不到從該語句開始執行後的未提交的修改（語句級讀一致性）。當ORACLE執行SELECT語句時，ORACLE依照當前的系統改變號（SYSTEM CHANGE NUMBER-SCN）來保證任何前於當前SCN的未提交的改變不被該語句處理。可以想象：當一個長時間的查詢正在執行時，若其他會話改變了該查詢要查詢的某個資料塊，ORACLE將利用回滾段的資料前影像來構造一個讀一致性檢視。

回滾段的型別

回滾段可分為 系統回滾段 和 非系統回滾段 , 其中非系統回滾段又分為 PUBLIC 回滾段 和 PRIVATE 回滾段 。

回滾段： 1 系統回滾段

2 非系統回滾段： (1)PUBLIC 回滾段

(2)PRIVATE 回滾段

系統回滾段用於處理涉及系統的 CATALOG 的事物 ( 比如大多數的 DDL), 它位於 SYSTEM 表空間 , 由於只有 SYSTEM 表空間可以隨時保持可用 , 因此 , 不要把 SYSTEM 回滾段放在其他的表空間中 .

原則 1: 系統回滾段應放在 SYSTEM 表空間中 , 並且應該永遠保持 ONLINE 狀態 .

PUBLIC 回滾段對於資料庫的所有例項 (INSTANCE) 都是可用的 , 除非將其顯式設定為 OFFLINE.

PRIVATE 回滾段是指對於資料庫的 某個例項是私有的 , 為了使用 PRIVATE 回滾段 , 某個例項應當在其 INITsid.ORA 的 ROLLBACK_SEGMENTS 中標明所有要使用的 PRIVATE 回滾段 , 或透過使用 ALTER ROLLBACK SEGMENT XXX ONLINE 來使用某一個回滾段 .

建議 1: 在單例項系統中 , 建議將所有回滾段設為 PUBLIC.

建議 2: 在多例項系統中 ( 如 OPS,RAC), 建議將每個例項的 PRIVATE 回滾段放置到訪問比較快的本地裝置上 .

回滾段的數量規劃

　　對於OLTP系統，存在大量的小事務處理，一般建議：

　　數量多的小回滾段；每四個事務一個回滾段；每個回滾段不要超過十個事務。

　　對於批處理，一般建議：

　　少的大回滾段；每個事務一個回滾段。

回滾段的使用

　　分配回滾段：當事務開始時， ORACLE 將為該事務分配回滾段，並將擁有最少事務的回滾段分配給該事務。事務可以用以下語句申請指定的回滾段：

　　 SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment

　　事務將以順序，迴圈的方式使用回滾段的區（ EXTENTS ），噹噹前區用滿後移到下一個區。幾個事務可以寫在回滾段的同一個區，但每個回滾段的塊只能包含一個事務的資訊。

　　例如（兩個事務使用同一個回滾段，該回滾段有四個區）：

　　 1 、事務在進行中，它們正在使用回滾段的第三個區；

　　 2 、當兩個事務產生更多的回滾資訊，它們將繼續使用第三個區；

　　 3 、當第三個區滿後，事務將寫到第四個區，當事務開始寫到一個新的區時，稱為翻轉（ WRAP ）；

4 、當第四個區用滿時，如果第一個區是空閒或非活動（使用該區的所有事務完成而沒有活動的事務）的，事務將接著使用第一個區。

回滾段的查詢

1 查詢資料庫的的回滾段情況

select owner,segment_id,segment_name,tablespace_name,status from dba_rollback_segs;

2 檢視系統回滾段基本資訊

select segment_name,tablespace_name,bytes,next_extent from dba_segments where segment_type='ROLLBACK';

從上面僅僅是查詢到回滾段的基本資訊，要了目前各個回滾段的動態資訊，還要查詢 V$ROLLNAME 和 V$ROLLSTAT 檢視。 V$ROLLNAME 檢視只存放各回滾段的編號和名字， V$ROLLSTATS 存放各個回滾段當前的情況資訊。要想得到每個回滾段的資訊，就要查詢兩個表的資訊才能得到。如：

select s.usn,n.name,s.extents,s.optsize,s.hwmsize,s.status from v$rollstat s, v$rollname n where s.usn=n.usn

2 檢視回滾段的使用情況，哪個使用者正在使用回滾段的資源 ( 當提交或回滾後資源釋放 ):

SELECT s.username, u.name FROM v$transaction t, v$rollstat r, v$rollname u, v$session s WHERE s.taddr = t.addr AND t.xidusn = r.usn AND r.usn = u.usn ORDER BY s.username;

3 回滾段當前活動的事物 ( 事務提交或回滾後自動清空 )

SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s,v$transaction t;

4 分析 UNDO 的使用情況

SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME, STATUS;

5 監控 undo 表空間

SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,MAXCONCURRENCY AS "MAXCON"　FROM V$UNDOSTAT;

6 查詢是否有回滾段的爭用

select * from v$waitstat;

SELECT name, waits, gets, waits/gets "Ratio" FROM v$rollstat a, v$rollname b WHERE a.usn = b.usn;

7 檢視回滾段的統計資訊：

SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn;

8 查詢回滾段的事務回退率

transaction rollbacks/(transaction rollbacks+user commits)

select name,value from v$sysstat where name in('user commits','transaction rollbacks');

9 查詢回滾段在使用 , 擴充套件 , 回縮的時候 extent 在迴圈的次數

select usn,wraps from v$rollstat;

10 查詢回滾段收縮的情況

select usn,optsize,shrinks from v$rollstat;

建立回滾段

　　語法：

　　 CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment

　　　　 [TABLESPACE tablespace]

　　　　 [STORAGE ([INITIAL integer[K|M]] [NEXT integer[K|M]]

　　　　　　　　　　 [MINEXTENTS integer]

　　　　　　　　　　 [MAXTENTS {integer|UNLIMITED}]

　　　　　　　　　　 [OPTIMAL {integer[K|M]|NULL}]) ]

　　注：

　　回滾段可以在建立時指明 PRIVATE 或 PUBLIC ，一旦建立將不能修改。

　　 MINEXTENTS 必須大於等於 2

　　 PCTINCREASE 必須是 0

　　 OPTIMAL 如果要指定，必須大於等於回滾段的初始大小（由 MINEXTENTS 指定）

　　建議：

　　一般情況下， INITIAL=NEXT

　　設定 OPTIMAL 引數來節約空間的使用

　　不要設定 MAXEXTENTS 為 UNLIMITED

　　回滾段應建立在一個特定的回滾段表空間內

引數的意義同建表上樣，但這裡不允許使用 pctincrease 引數，因此其增漲百分比總是零。這裡的引數的使用（執行效果）與表的引數有很大的不同。一表中的資料在關機後仍放於該表空間所對應的資料檔案中，而回滾段只在系統執行且在 insert ， update ， delete 時用，後即可釋放（讓別的 transaction 用），且在關機後所記錄的所有回滾資訊均被釋放，因而就有擴充套件後的空間如何釋放的問題，即保持多少空間為最佳的問題（由 OPTIMAL ）設定。 Optimal 可以避免“ snapshot too old ”錯誤的發生，為每個 rollback segmal 指定一個 optimal 以便在記憶體中保持一個較少的 cache 值，從而提高效能。

例：

create tablespace rbs datafile '/data/oradata/cts/rbs01.dbf' size 100M autoextend on next 10M maxsize 150M;

create public rollback segment rbs01 tablespace rbs storage(initial 100K next 100K minextents 10 maxextents 100 optimal 1000K);

select segment_name,tablespace_name,status from dba_rollback_segs ;

但是：建立成功後也查詢不到，因為從 9i 開始，回滾段開始自動管理，不需要手動建立

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

如果想改回手動管理需要執行一下操作：

ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE

Alter system set undo_management=MANUAL scope=spfile;

shutdown immediate

startup

select segment_name,owner,status from dba_rollback_segs;

改變回滾段

當回滾段建立好了之後，有時需要對它們進行修改。可以對回滾段進行儲存引數的修改，可以對某個離線（ OFFLINE ）回滾段修改為聯機（ ONLINE ）。也可能對已經處於聯機的回滾段設定為離線，比如當我們進行 IMP 或大量修改資料時， ORACLE 總是需要大的回滾段。但由於分配回滾段是 ORACLE 系統包辦。為了在事務處理中得到大的回滾段，我們只能 將較小的回滾段設定為離線的狀態 ，等到我們所處理的事務完成後在將它們設定為聯機等。

設定例項重啟動後回滾段聯機

除了上面使用 CREATE ROLLBACK SEGMENT 命令完成回滾段的建立外，要使 ORACLE 系統關閉重新啟動後仍然處於聯機，還要在 initsid.ora 引數檔案中 rollback_segments 引數加上回滾段的名字，如：

rollback_segments= (r0 ， r1 ， r2 )

使回滾段線上

　　當回滾段建立後，回滾段是離線的，不能被資料庫使用，為了使回滾段被事務利用，必須將回滾段線上。可以用以下命令使回滾段線上：

　　 ALTER ROLLBACK SEGMENT rollback_segment ONLINE;

　　例：

　　 ALTER ROLLBACK SEGMENT rbs01 ONLINE;

　　為了使回滾段在資料庫啟動時自動線上，可以在資料庫的引數檔案中列出回滾段的名字。例如在引數檔案中加入以下一行：

ROLLBACK_SEGMENT=(rbs01,rbs02)

select name,status,gets,waits from v$rollname,v$rollstat where v$rollstat.usn=v$rollname.usn;

回滾段的擴張（ EXTEND ）

噹噹前回滾段區的所有塊用完而事務還需要更多的回滾空間時，回滾段的指標將移到下一個區。當最後一個區用完，指標將移到第一個區的前面。回滾段指標移到下一個區的前提是下一個區沒有活動的事務，同時指標不能跨區。當下一個區正在使用時，事務將為回滾段分配一個新的區，這種分配稱為回滾段的擴充套件。回滾段將一直擴充套件到該回滾段區的個數到達回滾段的引數 MAXEXTENTS 的值時為止。

回滾段擴充套件次數的限制

在比較舊的 ORACLE 版本里，回滾段的擴充套件次數受到一些限制，它與例項的塊大小有關。比如：

當 ORACLE 塊＝ 2k 時， maxextents 121

當 ORACLE 塊＝ 4k 時， maxextents 249

當 ORACLE 塊＝ 8k 時， maxextents 505

當 ORACLE 塊＝ 16k 時， maxextents 1017

當 ORACLE 塊＝ 32k 時， maxextents 2041

注：在 ORACLE V7.3 以後， Maxextents 的擴充套件次數已被取消，你可以設得足夠大。甚至可以設為 UNLIMITED ( =249 000 000 ) 。

回滾段的回收和 OPTIMAL 引數

OPTIMAL 引數指明回滾段空閒時收縮到的位置，指明回滾段的 OPTIMAL 引數可以減少回滾段空間的浪費。

設定 OPTIMAL 引數

由於回滾段是一個動態的物件，它在使用中根據增刪改的資料量而增加（擴充套件），但在用完後就被釋放，然後另外的事務可以用。為了避免某個回滾段在擴充套件後總佔用量的空間，不利於另外的回滾段的擴充套件， ORACLE 提供了 optimal 參對回滾段進行控制，該引數的意義是回滾段在擴充套件過程中，當事務完成後（發 commit ， rollback 後），將回滾段的大小按照 optimal 值進性收縮。

需要注意的是， optimal 引數的設定不能比已經分配的空間還小。也就是說， optimal 的設定要比初始的擴充套件的還大才正確。比如：

一般 optimal = minextents * initial +

( minextents + n ) * next

其中，要求 minextents>=1 ; n>= 1

例如：

CREATE ROLLBACK SEGMENT rbs21 tablespace rbs

STORAGE( initial 10m next 2m minextents 2 optimal 16M );

這裡 optimal 只能填 14m 或 16m 或 18m 等，不能填 12m, 因為最小擴充套件為 2 次，即 rbs21 回滾段的開始分配已經是 10m + 2m = 12m ，根據 optimal 要大於初始值的原則，所以 optimal 至少從 14m 開始。

例子：

systen 回滾段的初始分配 initial = 409600 ；

下一次分配 next = 57344 ；

最佳擴充套件值 optimal= null ；

根據這樣的情況，我們可以修改下一次分配的值為 1MB(=1024000 位元組 ) ；修改最佳擴充套件值為： optimal = initial + 2 * next = 409600 + 2* 1024000 = 2457600 。所以修改 SYSTEM 回滾的命令為：

SQL> alter rollback segment system storage(next 1m optimal 2457600);

注： optimal 表示在事務處理當中，該回滾段由於增、刪、改的資料量的需要而不斷對回滾段進行擴充套件，以滿足事務的要求，但是在事務處理完成後可以讓回滾段縮小到一個最佳的範圍內。這就是由回滾段的 OPTIMAL 引數來控制。一般 optimal = initial + n* next , 且 n >1 。

修改回滾段的儲存引數

　　可以使用 ALTER ROLLBACK SEGMENT 命令修改回滾段的儲存引數（包括 OPTIMAL ， MAXEXTENTS ）。

　　語法：

　　 ALTER ROLLBACK SEGMENT rollback_segment

　　 [STORAGE ([NEXT integer[K|M]]

　　　　　　 [MINEXTENTS integer]

　　　　　　 [MAXEXTENTS {integer|UNLIMITED}]

　　　　　　 [OPTIMAL {integer[K|M]|NULL}]) ]

　　例：

　　 ALTER ROLLBACK SEGMENT rbs01 STORAGE (MAXEXTENTS 1000);

回收回滾段的空間

　　如果指定了回滾段的 OPTIMAL 引數， ORACLE 將自動回收回滾段到 OPTIMAL 指定的位置。使用者也可以手動回收回滾段的空間。

　　語法：

　　 ALTER ROLLBACK SEGMENT rollback_segment SHRINK [TO integer [K|M]];

　　說明：

　　如果不指明 TO integer 的數值， ORACLE 將試圖回收到 OPTIMAL 的位置。

　例：

　　 ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 2M;

使回滾段離線

select name,status,gets,waits from v$rollname,v$rollstat where v$rollstat.usn=v$rollname.usn;

　　為了達到以下兩個目的將要回滾段離線：

　　 1. 阻止新的事務使用該回滾段；

　　 2. 該回滾段必須刪除。

　　語法：

　　 ALTER ROLLBACK SEGMENT rollback_segment OFFLINE;

　　例：

　　 ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

　　說明：

　　如果有事務正在使用該回滾段，執行該命令後，回滾段的狀態將是 PENDING OFFLINE 。事務結束後，狀態將改為 OFFLINE ，可以透過 V$ROLLSTAT 查詢回滾段的狀態。

觀察回滾段的增長

雖然回滾段在使用過程中作過擴充套件而在用完後又自動按照 OPTIMAL 要求大小縮小，但在動態字典 V$ROLLSTAT 中會記錄曾經擴充套件的情況。命令如下：

select n.name, optsize, hwmsize from v$ROLLNAME n , V$ROLLSTAT s where n.usn=s.usn ;

optsize 是最優大小值， hwmsize =Hight water mark of rollback segment size 回滾段擴充套件中最高值（水位）。如果 optsize 為空，則 hwmsize 就是當前最高值。

增加 ORACLE 系統 rbs 表空間資料檔案

alter tablespace rbs add datafile '/disk1/ORACLE/oradata/ora8/rbs02.dbf' size 120m ;

在事務中指定使用回滾段

由於回滾段建立好了後，在處理中回滾段的使用是由系統安排的，這樣就有可能在處理中 實際需要較大的回滾段而系統總是分配較小的回滾而導致處理失敗 。為避免這樣的失敗出現。就要在命令中或程式中用 SET TRANSACTION USE ROLLBACK SEGMENT 命令來實現。

需要注意的是，在程式中使用設定命令時，要在程式的開始來使用 SET TRANSACTION 語句，而且在每次的 COMMIT 或 ROLLBACK 語句後都要重新使用 SET TRANSACTION 語句，如果在 COMMIT 或 ROLLBACK 語句後不再使用 SET TRANSACTION 語句，系統會釋放原來被分配的回滾段而隨機分配新的回滾段。下面給出在 SQL 下和在 PL/SQL 下使用回滾段的例子。

在執行大事務時 , 有時 oracle 會報出如下的錯誤 :

ORA-01555 :snapshot too old (rollback segment too small)

這說明 oracle 給此事務隨機分配的回滾段太小了 , 這時可以為它指定一個足夠大的回滾段 , 以確保這個事務的成功執行。

set transaction use rollback segment roll_abc;

delete from table_name where ...

commit;

回滾段 roll_abc 被指定給這個 delete 事務 ,commit 命令則在事務結束之後取消了回滾段的指定 .

1. 在 SQL> 下使用回滾段：

在 SQLPLUS 下進行任何大量的 INSERT 、 UPDATE 和 DELETE 都要用到回滾段，如何你希望操作能成功的話，你應該在操作前指定要用大的回滾段。如：

Commit;

Set transaction use rollback segment r1;

Delete from ...

Commit;

Set transaction use rollback segment r1;

例子：為大事務建立大的回滾段：

create rollback segment interest tablespace interest_tabspace

storage( initial 50m next 10m optimal 80m pct_increase 0 );

確定回滾段的數目

回滾段的數量直接影響到系統的效能，如果回滾段的個數不夠多，則在多個使用者同時進行增、刪、該時就存在等待現象。

要確定是否增加回滾段的數量，先要查詢兩個動態檢視，即 V$ROLLSTAT ， V$WAITSTAT 。如：

SQL> select * from v$waitstat where class='undo header';

CLASS COUNT TIME

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

undo header 0 0

SQL> select usn,extents,waits from v$rollstat;

USN EXTENTS WAITS

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

0 5 0

1 8 0

2 8 0

3 8 0

4 8 0

5 8 0

6 8 0

7 8 0

如果 waits 存在大於 0 的數，則需要增加回滾段的數量。一般回滾段的數量主要根據應用系統的型別來決定。比如一般的歷史檔案系統，由於其主要處理是查詢。這類增、刪、改相對較少的應用，可以建立較少的回滾段。而想銀行、證券等應用。就需要很多的回滾段。那麼到底要多少才相對就夠了呢？下面的回答具有一般性：

在併發程度要求高的應用系統，同一時間內有多個 transaction 在競爭竟爭回退段。假如 transaction 為事務的個數；則有：

n= transaction/transactions_per_rollback_segment

其中：

n= 回退段數量

transaction 為 ORACLE 系統引數，系統中允許併發處理的最大 transaction 數。

transactions_per_rollback_segment 為 ORACLE 引數，每個回退段允許同時寫入的最大 transaction 數。

另外，如果從 v$waitstat 動態檢視中查出自資料庫啟動後回退頭的等待次數較高，則應該多建立回滾段。

建立通用回滾段

無論是任何型別的應用，建議重新建立新的回滾段。除了上面介紹的建立專門的回滾段以外，需要建立初始值，下次增加及最佳值合適的值。一般建議是初始值最好是 5MB 以上，下次增長為 2MB 至 5MB 之間，最佳值在 20M 至 30MB 之間。

刪除已存在的回滾段 r01

　　當回滾段不再需要或要重建以改變 INITIAL ， NEXT 或 MINEXTENTS 引數時，可以將其刪除。要刪除回滾段，不許使該回滾段離線。

語法：

　　 DROP ROLLBACK SEGMENT rollback_segment;

例：

　　 DROP ROLLBACK SEGMENT rbs01;

查詢回滾段的資訊

所用資料字典： DBA_ROLLBACK_SEGS

alter rollback segment r01 offline;

drop rollback segment r01;

回滾段使用量的估算

如何保證有足夠的回滾段，滿足多個併發的 transaction 同時使用，又要考慮應有足夠大的回滾段來滿足特殊的 transaction( 如 long-runing transaction) 的需要。這就是如何考慮回滾段的數量和大小的問題。除了 system 回滾段外，通常還要建立多個回滾段，通常較短的 transaction 適於使用較小的回滾段，這將使系統將使系統有較好效能（因為大量的回滾資訊可快取於 sga 中。從而減少對硬碟的 I/O ）。而大的 transaction 則需要使用較大的回滾段，因為大量的回滾資訊可以存入事先分配的 extent 中避免了動態分配空間；同時也防止 transaction 執行期間，用盡回滾段的空間後，而導致資料庫出現 ora-01562 錯誤。

1 、回滾資訊量

儲存於回滾段中的回滾資訊量取決於 transaction (insert ， update ， delete ) 型別和實際處理資料量，通常， insert 向表中插入一第記錄 所產生的回滾資料 小於 delele 從表中刪除一條記錄 。因為回滾 insert 所產生的記錄只是需刪除它，而回滾一條刪除記錄需要重新插入該記錄，前者在回滾段中只儲存 rowid ，後者則儲存了重新這條記錄的所有資訊。

2 ．回滾資料量的估計

到目前為止， ORACLE 不能提供一個很好的回滾段資料量的計算，作為 dba 可以用下面方法進行估算：

1) 建立一個較小的測試表（資料來自實際表 EMP ）；

create table emp1 as select * from emp where deptno <=10;

2) 模仿（實際 updeste 語句） transaction 作如下測試：

update emp1 set deptno=1050 where deptno<=100;

3) 利用 v$rollstat

依據以上三步可以在 SQL> 下進行如下操作，從而得出基本回滾資訊量：

SQL>select sum(writes) "begin" from sys.v$rollstat;

SQL>update emp1 set deptno=1000 where deptno <=100;

SQL>select sum(writes)"end" from sys.v$rollstat;

小表的資訊量 = end - begin = test

實際 transaction 的回滾資訊量 = test * (emp_row / emp1_row ) * 1.05

由於一個事務的全部回滾資訊可以寫入多個 extent 中，但每個回滾段的 extent 數量受到限制。所以在設定 storage 的 initial ， next 引數時要小心，一般回滾段中的 extent 保持在 10 到 20 為理想。為了在 extent 後的空間部分被釋放，在 STORAGE 中提供 optimal 引數用以控制回滾段的大小。當回滾段需要分配更多的空間超過 optimal 值後， RDBMS 將檢查回滾段的大小，一旦額外分配的 extent 中無工作的事務且回滾段頭正在指向 extent 時，系統將釋放這個 extent 回滾段的大小向回縮，直至到 optimal 指定值。

回滾段的問題及解決方法

(1) 事務要求的回滾段空間不夠，表現為表空間用滿（ ORA-01560 錯誤），回滾段擴充套件到達引數 MAXEXTENTS 的值（ ORA-01628 ）。

解決方法：
A. 擴大回滾段所在表空間
B. 設定較大的MAXEXTENTS引數
C. 為回滾段設定OPTIMAL引數
D. 用較大的EXTENT引數重新建立回滾段

向回滾段表空間新增檔案或使已有的檔案變大；增加MAXEXTENTS的值。

ORA-01562: failed to extend rollback segment number 12

ORA-01628: max # extentsreached for rollback segment RBS12

擴大表空間

給回滾段表空間增加資料檔案，並設定大回滾段 apprbs 的 maxextents 值為無限大：

alter tablespace rbs add datafile ‘/opt/oracle/db02/oradata/ORCL/rbs02.dbf‘ size 8192m autoextend on next 10m maxsize unlimited;

擴大引數
ALTER ROLLBACK SEGMENT rbs01 STORAGE (MAXEXTENTS 1000);
可用如下語句代替 ( 批次提交釋放回退段空間 ):

1

create table tt(id number,sal number,age number);

2

declare

begin

for i in 1..10000 loop

insert into tt values(i,i*10,i*100);

end loop;

end;

select * from tt order by id;

刪除表 tt 中 id 不等於 10 的所有資料

3

begin

loop

delete from tt where id !=10 and rownum<=10;

exit when sql%notfound;

commit;

end loop;

end;

其中 rownum <=10的目的是每 10 條提交一次；

(2) ORA-01552 cannot use system rollback segment for non-system tablespace

'string'

原因 : 沒有可用的非系統回滾段 . 分為以下情形 :

A. 除了系統回滾段 , 未建立其它回滾段

B. 只建立了 PRIVATE 回滾段 , 但 INITsid.ORA 的 ROLLBACK_SEGMENTS 中未列出這些回滾段

C. 建立了 PUBLIC 回滾段 , 但這些回滾段都處於 OFFLINE 狀態

解決方法 : 根據以上原因相應解決即可

(3) ORA_01555 snapshot too old: rollback segment number string with name "string" too small

原因可分為以下情形：

A. 回滾段太少 / 太小

資料庫中有太多的事務修改資料並提交 , 就會發生已提交事務曾使用的空間被重用 , 從而造成一個 延續時間長的查詢所請求的資料已經不在回滾段中

( 即：長查詢開始之前，事務被修改並且沒有提交，長查詢進行中，事務提交，並且事務所在回滾段被其他事務覆蓋，這時就會出現 ora-01555 錯誤 )

解決方法 : 建立更多的回滾段 , 為回滾段設定較大的 EXTENT 以及較大的 MINEXTENTS

B. 回滾段被破壞

由於回滾段被破壞 , 造成事務無法將修改前的內容 (read-consistent snapshot) 放入回滾段 , 也會產生 ORA-01555 錯誤 .

( 即：事務被修改並且沒有提交，之後事務所在回滾段損壞，這時在查詢這個事務時就會報 ora-01555 錯誤 )

解決方法 : 將被破壞的回滾段 OFFLINE, 刪除重建 .

C. FETCH ACROSS COMMIT

當一個程式開啟一個 CURSOR, 然後迴圈執行 FETCH, UPDATE, COMMIT, 如果更新的表與 FETCH 的是同一個表 , 就很可能發生 ORA-01555 錯誤 .

解決方法 :

a. 使用大的回滾段

b. 減少提交頻率 ( 可參見本論壇 " 如何避免一個 PROCEDURE 被重複呼叫 " 一貼中 , 無名朋友的回帖 )

以上兩種方法只能減少該錯誤發生的可能 , 不能完全避免 . 如果要完全避免 , 須從執行方法著手 , 可以用以下兩種方法 :

c. 建立一個臨時表 , 存放要更新的表的查詢列 ( 如主鍵及相關的條件列 ), 從臨時表 FETCH, 更新原來的表 .

d. 捕獲 ORA-01555 錯誤 , 關閉並重新開啟 CURSOR, 繼續執行迴圈 :

D. 延時塊清除

* Delayed logging block cleanout( 延時塊清除 ) 是 ORACLE 用來提高寫效能的一種機制 : 當修改操作 (INSERT/UPDATE/DELETE) 發生時 , ORACLE 將原有的內容寫入回滾段 , 更新每個資料塊的頭部使其指向相應的回滾段 , 當該操作被 COMMIT 時 , ORACLE 並不再重新訪問一遍所有的資料塊來確認所有的修改 , 而只是更新位於回滾段頭部的事務槽來指明該事務已被 COMMIT, 這使得寫操作可以很快結束從而提高了效能接下來的任何訪問該操作所修改的資料的操作會使先前的寫操作真正生效 , 從而訪問到新的值 . Delayed logging block cleanout 雖然提高了效能 , 但卻可能導致 ORA-01555. 這種情況下 , 在 OPEN/FETCH 前對該表做全表掃描 ( 保證所有的修改被確認 ) 會有所幫助 .

E 不適當的 OPTIMAL 引數 :

太小的 OPTIMAL 引數會使回滾段很快被 SHRINK, 造成後續讀取操作訪問時 , 先前的內容已丟失，仔細設計 OPTIMAL 引數 , 不要讓回滾段過於頻繁的 EXTEND/SHRINK 有助於問題的解決。

F DB BLOCK BUFFER 太小

如果讀一致性所請求的塊的先前內容在緩衝區中 , 那麼就不用去訪問回滾段 ，而如果緩衝區太小 , 使得先前版本的內容在 CACHE 中的可能性變小 , 從而必須頻繁的訪問回滾段來獲取先前的內容 , 這將大大增大 ORA-01555 發生的可能。

oracle 塊延遲清除 (delayed block cleanout) 理解

為了保證事務的回退和滿足多使用者的 CR ， oracle 引入了 undo 機制，由於 undo 是迴圈使用的，在一個事務完成過程中，它與 redo 相互配合，其中 undo 在一次事務中需要完成以下工作：

(1) Transaction 開始前回滾段獲取一個 ITL( 事務槽 ) ，分配空間，記錄事務資訊

(2) Transaction 提交後， redo 完成記錄，同時還清除回滾段的事務資訊包括行級鎖， ITL 資訊 (commit 標誌， SCN 等 )

清除這些事務段的資訊的過程就叫做塊清除，在完成塊清除時 , 我們本事務修改的資料塊就會存在兩種可能

(1) 所有的資料塊還儲存在 buffer cache 中

(2) 部分資料塊或者是全部資料塊由於 LRU 管理已經被刷出了 buffer cache 。

oracle 為了考慮到塊清除的成本，以及效能，會作以下兩種方式的塊清除處理：

(1) 快速塊清除 (fast block cleanout), 當事務修改的資料庫全部儲存在 buffer cache 並且修改資料塊的資料量沒有超過 cache buffer 的 10% ，快速清除事務資訊。

(2) 延遲塊清除 (delayed block cleanout) 當修改的資料塊的閥值超過 10% 或者本次事務相關的資料塊已經被刷出了 buffer cache ， oracle 會下次訪問此 block 時再清除事務資訊。

參考：

http://blog.163.com/xb_guo/blog/static/2008392472011112613232758/

http://blog.csdn.net/47522341/article/details/3862678

http://blog.itpub.net/8117479/viewspace-705530/

ORACLE 回滾段詳解

相關文章