Oracle undo 表空間管理
初步瞭解undo表空間
undo表空間用於存放undo資料。當使用者執行DML操作時，undo資料被放在undo段，可以回退事物。執行一個事物時，新資料放在資料段中，如果事物存在問題，也可以用undo資料來恢復資料。Oracle的Undo有兩種方式： 一是使用undo 表空間，二是使用回滾段。
我們透過 undo_management 引數來控制使用哪種方式，如果設為auto，就使用UNDO 表空間，這時必須要指定一個UNDO 表空間。如果設為Manual，系統啟動後使用rollback segment方式儲存undo資訊。如果系統沒有指定undo_management，那麼系統預設以Manual方式啟動,即使設定了auto方式的引數，這些引數將被忽略。
當例項啟動的時候，系統自動選擇第一個有效的undo表空間或者是rollback segment，如果沒有有效的可用的undo表空間或者是回滾段，系統使用system rollback segment。這種情況是不被推薦的，當系統執行在沒有undo的情況下，系統會在alert.log中記錄一條警告資訊。
UNDO_MANAGEMENT用於指定undo資料的管理方式。如果使用自動管理，必須設定為AUTO；如果手動管理，必須設定為MANUAL。
使用自動管理的時候，oracle會使用UNDO表空間管理UNDO資料；使用手動管理時，oracle會使用回滾段管理UNDO資料。
如果使用自動管理模式，必須建立UNDO表空間，並且配置UNDO_TABLESPACE引數，否則oracle會使用SYSTEM回滾段存放UNDO紀錄，並在警告日誌中紀錄提示。
undo tablespace 表空間的大小由3個引數決定：
1、block的大小，一般為8192bytes
2、每分鐘產生的undo資料。
3、undo_retention的大小，一般為900秒，意思就是隻要undo 表空間允許，undo資料就可以儲存900秒。
ora-30012錯誤是由於undo表空間導致的。如果我們undo表空間錯誤，要我們重新建立，一般我們可以在引數檔案中修改這兩個引數
undo_management=’manual’,undo_tablespace=’system’ 再啟動服務就可以重新起來資料庫了，這時要建立undo表空間，用 create undo tablespace undotbs datafile ‘ e:\oradata\whx\undotbs.dbf’ size 100m;這樣建立就行了，然後把相應的undo引數改回去undo_management=’auto’,undo_tablespace=’undotbs’ 就可以，最後把資料庫起來。
一個資料庫只有一個undo表空間，但rac要兩個undo表空間，因為一個UNDO不能由多個例程同時使用。
建立UNDO表空間
1、使用CREATE DATABASE命令建立UNDO表空間
如果使用的是UNDO管理模式，但是沒有指定UNDO TABLESPACE，那麼建立資料庫的時候oracle會自動生成名稱為SYS_UNDOTBS的UNDO表空間。
2、使用CREATE UNDO TABLESPACE 命令建立UNDO表空間
建立資料庫以後，可以使用該命令建立UNDO表空間。
3、修改UNDO表空間
當事務用盡UNDO表空間後，增加資料檔案：ALTER TABLESPACE … ADD DATAFILE;
當UNDO表空間所在磁碟填滿時，移動資料檔案到其他磁碟：
ALTER TABLESPACE …RENAME DATAFILE;
當在OPEN狀態下移動UNDO表空間時，可以使表空間離線/聯機：
ALTER TABLESPACE … OFFLINE/ONLINE;
當資料庫處於歸檔模式時，備份UNDO表空間：
ALTER TABLESPACE … BEGIN BACKUP/END BACKUP
切換UNDO表空間
啟動並開啟oracle資料庫後，同一時刻只能使用一個UNDO表空間。可以切換UNDO表空間：
ALTER SYSTEM SET undo_tablespace=undotbs02;
刪除UNDO表空間
當前使用的UNDO表空間不能被刪除，如果要刪除當前例項使用的UNDO，需要先切換，然後刪除:
DROP TABLESPACE undotbs01;
監控UNDO表空間的使用：
1、確定當前正在使用的undo表空間：
show parameter undo_tablespace
Oracle 的Undo有兩種方式： 一是使用undo 表空間，二是使用回滾段.
我們透過 undo_management 引數來控制使用哪種方式，如果設為auto，就使用UNDO 表空間，這時必須要指定一個UNDO 表空間。 如果設為man l，系統啟動後使用rollback segment方式儲存undo資訊。如果系統沒有指定undo_management，那麼系統預設以man l方式啟動,即使設定了auto方式的引數，這些引數將被忽略。
當例項啟動的時候，系統自動選擇第一個有效的undo表空間或者是rollback segment，如果沒有有效的可用的undo表空間或者是回滾段，系統使用system rollback segment。這種情況是不被推薦的，當系統執行在沒有undo的情況下，系統會在alert.log中記錄一條警告資訊。
SQL> show parameter undo
NAME TYPE VAL
———————————— ———– ——————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
一． UNDO 表空間
下面來看一下undo 的表空間管理。先來檢視一下表空間的使用情況：
SELECT a.tablespace_name,
ROUND (a.total_size) “total_size(MB)”,
ROUND (a.total_size) – ROUND (b.free_size, 3) “used_size(MB)”,
ROUND (b.free_size, 3) “free_size(MB)”,
ROUND (b.free_size / total_size * 100, 2) || ‘%’ free_rate
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);
TABLESPACE_NAME total_size(MB) used_size(MB) free_size(MB) FREE_RATE
—————- ————– ————- ————- ————–
SYSAUX 580 545.187 34.813 6%
UNDOTBS1 90 23.875 66.125 73.47%
DAVE 20 6.25 13.75 68.75%
USERS 10 8.375 1.625 16.25%
SYSTEM 960 951.062 8.938 93%
從結果我們看到UNDO 表空間已經用了23.875M。 我們看一下這使用的23M空間裡空閒和非空閒比例：
/* Formatted on 2010/6/23 9:49:53 (QP5 v5.115.810.9015) */
SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 “Bytes(M)”
FROM dba_undo_extents
GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS Bytes(M)
——————– ——— ———-
UNDOTBS1 UNEXPIRED 9.1875
UNDOTBS1 EXPIRED 13.6875
我們看一下查詢的結果，UNEXPIRED 和EXPIRED 是已使用的undo 表空間，其中expired 說明是已經過期的資料，也就是15分鐘（預設情況）以外的資料，以被覆蓋，可以認為是空閒的。
在此補充一點知識：
採用UNDO 表空間時，會有一個引數UNDO_RETENTION，該引數用來指定undo 記錄儲存的最長時間，以秒為單位，是個動態引數，完全可以在例項執行時隨時修改，通常預設是900 秒，也就是15 分鐘。
undo_retention 只是指定undo 資料的過期時間，並不是說，undo 中的資料一定會在undo表空間中儲存15 分鐘，比如說剛一個新事務開始的時候，如果undo 表空間已經被寫滿，則新事務的資料會自動覆蓋已提交事務的資料，而不管這些資料是否已過期，因此呢，這就又關聯回了第一點，當你建立一個自動管理的undo 表空間時，還要注意其空間大小，要儘可能保證undo 表空間有足夠的儲存空間。
undo_retention 中指定的時間一過，已經提交事務中的資料就立刻無法訪問，它只是失效，只要不被別的事務覆蓋，它會仍然存在，並可隨時被flashback 特性引用。如果你的undo表空間足夠大，而資料庫又不是那麼繁忙，那麼其實undo_retention 引數的值並不會影響到你，哪怕你設定成1，只要沒有事務去覆蓋undo 資料，它就會持續有效。因此呢，這裡還是那句話，要注意undo 表空間的大小，保證其有足夠的儲存空間。
只有在一種情況下，undo 表空間能夠確保undo 中的資料在undo_retention 指定時間過期前一定有效，就是為undo 表空間指定Retention G rantee，指定之後，oracle 對於undo 表空間中未過期的undo 資料不會覆蓋，例如：
SQL> Alter tablespace undotbs1 retention g rantee;
禁止undo 表空間retention g rantee，例如：
SQL> Alter tablespace undotbs1 retention nog rantee;
總結一下：
UNDO 表空間是會被重用的,只有當事務沒結束,或開了retention g rantee,或在undo_retention時間內不能被重用。
在undo_retention規定的時間內，資料都是有效的，過期後都會設為無效，狀態被改為Expired，這些回滾段將會被看作Free Space。但是隻要資料沒有被覆蓋就可以使用。如果空間已滿，新事務的資料會自動覆蓋掉已經提交的事務資料，即使在undo_retention的時間內。除非指定Retention G rantee模式，才能保證在undo_retention內不被覆蓋。
二． UNDO 表空間滿了的處理方法
2.1 先模擬UNDO 表空間滿的情況
SQL> alter system set undo_retention=10800; — 3個小時
系統已更改。
SQL> create undo tablespace undo datafile ‘F:\backup\undo.dbf’ size 1m ;
表空間已建立。
SQL> alter tablespace undo retention g rantee;
表空間已更改。
SQL> alter system set undo_tablespace=undo;
系統已更改。
SQL> create table DBA(id number);
表已建立。
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into dba val s(i);
4 commit;
5 end loop;
6 end;
7 /
begin
*
第 1 行出現錯誤:
ORA-30036: 無法按 8 擴充套件段 (在還原表空間 ‘UNDO’ 中)
ORA-06512: 在 line 3
2.2 處理方法
處理方法有兩種，一是新增undo 表空間的資料檔案，二是切換UNDO tablespace. 這種情況下多用在undo 表空間已經非常大的情況。
2.2.1 增加資料檔案
SQL> ALTER TABLESPACE undo ADD DATAFILE ‘F:\backup\undo02.dbf’ size 100M reuse;
表空間已更改。
SQL> begin
2 for i in 1..100000 loop
3 insert into dba val s(1);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
2.2.2 切換UNDO 表空間
1、建立新的表空間UNDOTBS2
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘F:\backup\undo03.dbf’ size 100M reuse;
表空間已建立。
2、切換到新建的UNOD表空間上來,操作如下
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
系統已更改。
3、將原來的UNDO表空間,置為離線:
SQL> alter tablespace UNDO offline;
表空間已更改。
4、刪除原來的UNDO表空間:
SQL> drop tablespace UNDO incl ing contents AND DATAFILES CASCADE CONSTRAINTS ;
表空間已刪除。
如果只是drop tablespace UNDO ，則只會在刪除控制檔案裡的記錄，並不會物理刪除檔案。
Drop undo表空間的時候必須是在未使用的情況下才能進行。如果undo表空間正在使用（例如事務失敗，但是還沒有恢復成功），那麼drop表空間命令將失敗。在drop表空間的時候可以使用incl ing contents。
三． UNDO 表空間損壞的恢復方法
一般Undo 表空間損壞的情況下，資料庫都已不能正常開啟了。啟動時都會報類似如下的錯誤：
ORA-01157: cannot identify/lock data file 12 – see DBWR trace file
ORA-01110: data file 12: ‘/d01/oramtest/proddata/undo01.dbf’
要想解決問題，必須重建UNDO 表空間，但是如果不open， 就不能重建建立undo 表空間。 所以可以先用系統預設的undo 表空間：system rollback segment 來啟動資料庫，再建立UNDO 表空間。
3.1 建立pfile 檔案
SQL> create pfile=’F:\initorcl.ora’ from spfile;
檔案已建立。
3.2 修改pfile檔案
#*.undo_tablespace=’UNDOTBS1′
#*.undo_management=’AUTO’
undo_management=’MAN L’
rollback_segments=’SYSTEM’
3.3 啟動資料庫至Mount 狀態
SQL> STARTUP MOUNT pfile=’F:\initorcl.ora’ ;
3.4 offline drop undo 表空間
SQL> ALTER DATABASE DATAFILE ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ OFFLINE DROP;
3.5 open 資料庫
SQL> ALTER DATABASE OPEN;
3.6 刪除舊的undo 表空間
SQL> DROP TABLESPACE UNDOTBS1 INCL ING CONTENTS;
注：該命令不會刪除物理檔案。 要想一起刪除物理檔案需要 AND DATAFILES CASCADE CONSTRAINTS ;
如： drop tablespace UNDOTBS1 incl ing contents AND DATAFILES CASCADE CONSTRAINTS ;
3.7 建立新的UNDO 表空間
SQL> create undo tablespace undotbs1 datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M ;
create undo tablespace undotbs1 datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M
*
第 1 行出現錯誤:
ORA-01119: 建立資料庫檔案 ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’時出錯
ORA-27038: 所建立的檔案已存在
OSD-04010: 指定了 <create> 選項, 但檔案已經存在
因為我們之前刪除時並沒有刪除物理檔案，所以在建同名檔案時就會報錯。 我們可以加上REUSE 引數。 只要檔案不在使用，就可以重寫已經存在的檔案。
SQL> create undo tablespace undotbs1 datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M reuse;
表空間已建立。
3.8 shutdown 資料庫 並將pfile 改回
SQL> select name,issys_modifiable from v$parameter where name=’undo_management’ or name=’rollback_segments’;
NAME ISSYS_MOD
——————– ———
rollback_segments FALSE
undo_management FALSE
從上面查詢的結果，可以知道修改這2個引數必須重啟資料，所以還是shutdown 吧。
SQL> shutdown immediate
3.9 修改pfile 引數
*.undo_tablespace=’UNDOTBS1′
*.undo_management=’AUTO’
#undo_management=’MAN L’
#rollback_segments=’SYSTEM’
3.10 用剛才修改的pfile 啟動資料庫，並建立spfile
SQL> startup pfile=’F:\initorcl.ora’ ;
SQL> create spfile from pfile=’F:\initorcl.ora’;
3.10 再次shutdown，用spfile 啟動.
SQL> shutdown immediate
SQL> startup
一般資料檔案損壞的情況也可以採用類似的方法， 先啟動到mount， 在將損壞的資料檔案offline drop。 在open 資料庫，drop 掉損壞的資料檔案。 當然這種做法有資料丟失。能恢復的話，儘量恢復。
下面的sql語句查詢undo表空間的使用：—-最佳化出現的sql語句
SQL> SELECT e.sql_text,r.name 回滾段名,
s.serial#,s.sid,s.username 使用者名稱,
t.used_ublk*8192/1024/1024||’M’ 使用大小,
substr(s.program, 1, 78) 操作程式
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r,sys.v_$sqlarea e
WHERE t.addr = s.taddr and t.xidusn = r.usn and e.address=DECODE(s.sql_hash_value, 0, s.prev_sql_addr, s.sql_address) order by t.USED_UBLK desc ;
—檢視undo表空間使用的表空間下還有多少可以回退的空間，EXPIRED是可以會退的，ACTIVE是正在用的，UNEXPIRED是系統保留的和undo_retention=600有關。。。。。
SQL> SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 “Bytes(M)”
FROM dba_undo_extents
GROUP BY tablespace_name, status;
undo表空間用於存放undo資料。當使用者執行DML操作時，undo資料被放在undo段，可以回退事物。執行一個事物時，新資料放在資料段中，如果事物存在問題，也可以用undo資料來恢復資料。Oracle的Undo有兩種方式： 一是使用undo 表空間，二是使用回滾段。
我們透過 undo_management 引數來控制使用哪種方式，如果設為auto，就使用UNDO 表空間，這時必須要指定一個UNDO 表空間。如果設為Manual，系統啟動後使用rollback segment方式儲存undo資訊。如果系統沒有指定undo_management，那麼系統預設以Manual方式啟動,即使設定了auto方式的引數，這些引數將被忽略。
當例項啟動的時候，系統自動選擇第一個有效的undo表空間或者是rollback segment，如果沒有有效的可用的undo表空間或者是回滾段，系統使用system rollback segment。這種情況是不被推薦的，當系統執行在沒有undo的情況下，系統會在alert.log中記錄一條警告資訊。
UNDO_MANAGEMENT用於指定undo資料的管理方式。如果使用自動管理，必須設定為AUTO；如果手動管理，必須設定為MANUAL。
使用自動管理的時候，oracle會使用UNDO表空間管理UNDO資料；使用手動管理時，oracle會使用回滾段管理UNDO資料。
如果使用自動管理模式，必須建立UNDO表空間，並且配置UNDO_TABLESPACE引數，否則oracle會使用SYSTEM回滾段存放UNDO紀錄，並在警告日誌中紀錄提示。
undo tablespace 表空間的大小由3個引數決定：
1、block的大小，一般為8192bytes
2、每分鐘產生的undo資料。
3、undo_retention的大小，一般為900秒，意思就是隻要undo 表空間允許，undo資料就可以儲存900秒。
ora-30012錯誤是由於undo表空間導致的。如果我們undo表空間錯誤，要我們重新建立，一般我們可以在引數檔案中修改這兩個引數
undo_management=’manual’,undo_tablespace=’system’ 再啟動服務就可以重新起來資料庫了，這時要建立undo表空間，用 create undo tablespace undotbs datafile ‘ e:\oradata\whx\undotbs.dbf’ size 100m;這樣建立就行了，然後把相應的undo引數改回去undo_management=’auto’,undo_tablespace=’undotbs’ 就可以，最後把資料庫起來。
一個資料庫只有一個undo表空間，但rac要兩個undo表空間，因為一個UNDO不能由多個例程同時使用。
建立UNDO表空間
1、使用CREATE DATABASE命令建立UNDO表空間
如果使用的是UNDO管理模式，但是沒有指定UNDO TABLESPACE，那麼建立資料庫的時候oracle會自動生成名稱為SYS_UNDOTBS的UNDO表空間。
2、使用CREATE UNDO TABLESPACE 命令建立UNDO表空間
建立資料庫以後，可以使用該命令建立UNDO表空間。
3、修改UNDO表空間
當事務用盡UNDO表空間後，增加資料檔案：ALTER TABLESPACE … ADD DATAFILE;
當UNDO表空間所在磁碟填滿時，移動資料檔案到其他磁碟：
ALTER TABLESPACE …RENAME DATAFILE;
當在OPEN狀態下移動UNDO表空間時，可以使表空間離線/聯機：
ALTER TABLESPACE … OFFLINE/ONLINE;
當資料庫處於歸檔模式時，備份UNDO表空間：
ALTER TABLESPACE … BEGIN BACKUP/END BACKUP
切換UNDO表空間
啟動並開啟oracle資料庫後，同一時刻只能使用一個UNDO表空間。可以切換UNDO表空間：
ALTER SYSTEM SET undo_tablespace=undotbs02;
刪除UNDO表空間
當前使用的UNDO表空間不能被刪除，如果要刪除當前例項使用的UNDO，需要先切換，然後刪除:
DROP TABLESPACE undotbs01;
監控UNDO表空間的使用：
1、確定當前正在使用的undo表空間：
show parameter undo_tablespace
Oracle 的Undo有兩種方式： 一是使用undo 表空間，二是使用回滾段.
我們透過 undo_management 引數來控制使用哪種方式，如果設為auto，就使用UNDO 表空間，這時必須要指定一個UNDO 表空間。 如果設為man l，系統啟動後使用rollback segment方式儲存undo資訊。如果系統沒有指定undo_management，那麼系統預設以man l方式啟動,即使設定了auto方式的引數，這些引數將被忽略。
當例項啟動的時候，系統自動選擇第一個有效的undo表空間或者是rollback segment，如果沒有有效的可用的undo表空間或者是回滾段，系統使用system rollback segment。這種情況是不被推薦的，當系統執行在沒有undo的情況下，系統會在alert.log中記錄一條警告資訊。
SQL> show parameter undo
NAME TYPE VAL
———————————— ———– ——————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
一． UNDO 表空間
下面來看一下undo 的表空間管理。先來檢視一下表空間的使用情況：
SELECT a.tablespace_name,
ROUND (a.total_size) “total_size(MB)”,
ROUND (a.total_size) – ROUND (b.free_size, 3) “used_size(MB)”,
ROUND (b.free_size, 3) “free_size(MB)”,
ROUND (b.free_size / total_size * 100, 2) || ‘%’ free_rate
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);
TABLESPACE_NAME total_size(MB) used_size(MB) free_size(MB) FREE_RATE
—————- ————– ————- ————- ————–
SYSAUX 580 545.187 34.813 6%
UNDOTBS1 90 23.875 66.125 73.47%
DAVE 20 6.25 13.75 68.75%
USERS 10 8.375 1.625 16.25%
SYSTEM 960 951.062 8.938 93%
從結果我們看到UNDO 表空間已經用了23.875M。 我們看一下這使用的23M空間裡空閒和非空閒比例：
/* Formatted on 2010/6/23 9:49:53 (QP5 v5.115.810.9015) */
SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 “Bytes(M)”
FROM dba_undo_extents
GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS Bytes(M)
——————– ——— ———-
UNDOTBS1 UNEXPIRED 9.1875
UNDOTBS1 EXPIRED 13.6875
我們看一下查詢的結果，UNEXPIRED 和EXPIRED 是已使用的undo 表空間，其中expired 說明是已經過期的資料，也就是15分鐘（預設情況）以外的資料，以被覆蓋，可以認為是空閒的。
在此補充一點知識：
採用UNDO 表空間時，會有一個引數UNDO_RETENTION，該引數用來指定undo 記錄儲存的最長時間，以秒為單位，是個動態引數，完全可以在例項執行時隨時修改，通常預設是900 秒，也就是15 分鐘。
undo_retention 只是指定undo 資料的過期時間，並不是說，undo 中的資料一定會在undo表空間中儲存15 分鐘，比如說剛一個新事務開始的時候，如果undo 表空間已經被寫滿，則新事務的資料會自動覆蓋已提交事務的資料，而不管這些資料是否已過期，因此呢，這就又關聯回了第一點，當你建立一個自動管理的undo 表空間時，還要注意其空間大小，要儘可能保證undo 表空間有足夠的儲存空間。
undo_retention 中指定的時間一過，已經提交事務中的資料就立刻無法訪問，它只是失效，只要不被別的事務覆蓋，它會仍然存在，並可隨時被flashback 特性引用。如果你的undo表空間足夠大，而資料庫又不是那麼繁忙，那麼其實undo_retention 引數的值並不會影響到你，哪怕你設定成1，只要沒有事務去覆蓋undo 資料，它就會持續有效。因此呢，這裡還是那句話，要注意undo 表空間的大小，保證其有足夠的儲存空間。
只有在一種情況下，undo 表空間能夠確保undo 中的資料在undo_retention 指定時間過期前一定有效，就是為undo 表空間指定Retention G rantee，指定之後，oracle 對於undo 表空間中未過期的undo 資料不會覆蓋，例如：
SQL> Alter tablespace undotbs1 retention g rantee;
禁止undo 表空間retention g rantee，例如：
SQL> Alter tablespace undotbs1 retention nog rantee;
總結一下：
UNDO 表空間是會被重用的,只有當事務沒結束,或開了retention g rantee,或在undo_retention時間內不能被重用。
在undo_retention規定的時間內，資料都是有效的，過期後都會設為無效，狀態被改為Expired，這些回滾段將會被看作Free Space。但是隻要資料沒有被覆蓋就可以使用。如果空間已滿，新事務的資料會自動覆蓋掉已經提交的事務資料，即使在undo_retention的時間內。除非指定Retention G rantee模式，才能保證在undo_retention內不被覆蓋。
二． UNDO 表空間滿了的處理方法
2.1 先模擬UNDO 表空間滿的情況
SQL> alter system set undo_retention=10800; — 3個小時
系統已更改。
SQL> create undo tablespace undo datafile ‘F:\backup\undo.dbf’ size 1m ;
表空間已建立。
SQL> alter tablespace undo retention g rantee;
表空間已更改。
SQL> alter system set undo_tablespace=undo;
系統已更改。
SQL> create table DBA(id number);
表已建立。
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into dba val s(i);
4 commit;
5 end loop;
6 end;
7 /
begin
*
第 1 行出現錯誤:
ORA-30036: 無法按 8 擴充套件段 (在還原表空間 ‘UNDO’ 中)
ORA-06512: 在 line 3
2.2 處理方法
處理方法有兩種，一是新增undo 表空間的資料檔案，二是切換UNDO tablespace. 這種情況下多用在undo 表空間已經非常大的情況。
2.2.1 增加資料檔案
SQL> ALTER TABLESPACE undo ADD DATAFILE ‘F:\backup\undo02.dbf’ size 100M reuse;
表空間已更改。
SQL> begin
2 for i in 1..100000 loop
3 insert into dba val s(1);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
2.2.2 切換UNDO 表空間
1、建立新的表空間UNDOTBS2
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘F:\backup\undo03.dbf’ size 100M reuse;
表空間已建立。
2、切換到新建的UNOD表空間上來,操作如下
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
系統已更改。
3、將原來的UNDO表空間,置為離線:
SQL> alter tablespace UNDO offline;
表空間已更改。
4、刪除原來的UNDO表空間:
SQL> drop tablespace UNDO incl ing contents AND DATAFILES CASCADE CONSTRAINTS ;
表空間已刪除。
如果只是drop tablespace UNDO ，則只會在刪除控制檔案裡的記錄，並不會物理刪除檔案。
Drop undo表空間的時候必須是在未使用的情況下才能進行。如果undo表空間正在使用（例如事務失敗，但是還沒有恢復成功），那麼drop表空間命令將失敗。在drop表空間的時候可以使用incl ing contents。
三． UNDO 表空間損壞的恢復方法
一般Undo 表空間損壞的情況下，資料庫都已不能正常開啟了。啟動時都會報類似如下的錯誤：
ORA-01157: cannot identify/lock data file 12 – see DBWR trace file
ORA-01110: data file 12: ‘/d01/oramtest/proddata/undo01.dbf’
要想解決問題，必須重建UNDO 表空間，但是如果不open， 就不能重建建立undo 表空間。 所以可以先用系統預設的undo 表空間：system rollback segment 來啟動資料庫，再建立UNDO 表空間。
3.1 建立pfile 檔案
SQL> create pfile=’F:\initorcl.ora’ from spfile;
檔案已建立。
3.2 修改pfile檔案
#*.undo_tablespace=’UNDOTBS1′
#*.undo_management=’AUTO’
undo_management=’MAN L’
rollback_segments=’SYSTEM’
3.3 啟動資料庫至Mount 狀態
SQL> STARTUP MOUNT pfile=’F:\initorcl.ora’ ;
3.4 offline drop undo 表空間
SQL> ALTER DATABASE DATAFILE ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ OFFLINE DROP;
3.5 open 資料庫
SQL> ALTER DATABASE OPEN;
3.6 刪除舊的undo 表空間
SQL> DROP TABLESPACE UNDOTBS1 INCL ING CONTENTS;
注：該命令不會刪除物理檔案。 要想一起刪除物理檔案需要 AND DATAFILES CASCADE CONSTRAINTS ;
如： drop tablespace UNDOTBS1 incl ing contents AND DATAFILES CASCADE CONSTRAINTS ;
3.7 建立新的UNDO 表空間
SQL> create undo tablespace undotbs1 datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M ;
create undo tablespace undotbs1 datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M
*
第 1 行出現錯誤:
ORA-01119: 建立資料庫檔案 ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’時出錯
ORA-27038: 所建立的檔案已存在
OSD-04010: 指定了 <create> 選項, 但檔案已經存在
因為我們之前刪除時並沒有刪除物理檔案，所以在建同名檔案時就會報錯。 我們可以加上REUSE 引數。 只要檔案不在使用，就可以重寫已經存在的檔案。
SQL> create undo tablespace undotbs1 datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M reuse;
表空間已建立。
3.8 shutdown 資料庫 並將pfile 改回
SQL> select name,issys_modifiable from v$parameter where name=’undo_management’ or name=’rollback_segments’;
NAME ISSYS_MOD
——————– ———
rollback_segments FALSE
undo_management FALSE
從上面查詢的結果，可以知道修改這2個引數必須重啟資料，所以還是shutdown 吧。
SQL> shutdown immediate
3.9 修改pfile 引數
*.undo_tablespace=’UNDOTBS1′
*.undo_management=’AUTO’
#undo_management=’MAN L’
#rollback_segments=’SYSTEM’
3.10 用剛才修改的pfile 啟動資料庫，並建立spfile
SQL> startup pfile=’F:\initorcl.ora’ ;
SQL> create spfile from pfile=’F:\initorcl.ora’;
3.10 再次shutdown，用spfile 啟動.
SQL> shutdown immediate
SQL> startup
一般資料檔案損壞的情況也可以採用類似的方法， 先啟動到mount， 在將損壞的資料檔案offline drop。 在open 資料庫，drop 掉損壞的資料檔案。 當然這種做法有資料丟失。能恢復的話，儘量恢復。
下面的sql語句查詢undo表空間的使用：—-最佳化出現的sql語句
SQL> SELECT e.sql_text,r.name 回滾段名,
s.serial#,s.sid,s.username 使用者名稱,
t.used_ublk*8192/1024/1024||’M’ 使用大小,
substr(s.program, 1, 78) 操作程式
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r,sys.v_$sqlarea e
WHERE t.addr = s.taddr and t.xidusn = r.usn and e.address=DECODE(s.sql_hash_value, 0, s.prev_sql_addr, s.sql_address) order by t.USED_UBLK desc ;
—檢視undo表空間使用的表空間下還有多少可以回退的空間，EXPIRED是可以會退的，ACTIVE是正在用的，UNEXPIRED是系統保留的和undo_retention=600有關。。。。。
SQL> SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 “Bytes(M)”
FROM dba_undo_extents
GROUP BY tablespace_name, status;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28869493/viewspace-2123775/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle undo表空間管理Oracle
- oracle undo 表空間Oracle
- Oracle的UNDO表空間管理總結Oracle
- oracle重建UNDO表空間Oracle
- oracle UNDO表空間一個bug——undo表空間快速擴充套件Oracle套件
- 監控和管理Oracle UNDO表空間的使用Oracle
- ORACLE線上切換undo表空間Oracle
- Oracle undo表空間切換（ZT）Oracle
- oracle回滾段 undo 表空間Oracle
- ORACLE撤銷表空間（Undo Tablespaces）Oracle
- 10.管理UNDO表空間.(筆記)筆記
- 理解UNDO表空間
- Oracle - 回滾表空間 Undo 的整理Oracle
- Oracle undo 表空間使用情況分析Oracle
- 【UNDO】使用重建UNDO表空間方法解決UNDO表空間過大問題
- Oracle表空間管理Oracle
- Oracle 表空間管理Oracle
- 自動undo表空間模式下切換新的undo表空間模式
- Oracle切換undo表空間操作步驟Oracle
- Oracle基礎 03 回滾表空間 undoOracle
- Oracle undo表空間爆滿的解決Oracle
- undo表空間總結
- Oracle 表空間的管理Oracle
- Oracle的表空間管理Oracle
- Oracle 本地表空間管理與字典表空間管理Oracle
- oracle 釋放undo空間Oracle
- oracle中undo表空間丟失處理方法Oracle
- Oracle undo表空間爆滿的處理方法Oracle
- Oracle 釋放過度使用的Undo表空間Oracle
- MySQL InnoDB Undo表空間配置MySql
- undo表空間故障處理
- 如何計算自動管理的UNDO表空間大小
- Oracle OCP(49)：表空間管理Oracle
- oracle表空間日常操作管理Oracle
- oracle本地管理的表空間Oracle
- oracle表空間管理維護Oracle
- Oracle 19c 線上縮減 UNDO 表空間 容量Oracle
- ORACLE 中undo表空間爆滿的解決方法Oracle