UNDO表空間介紹
UNDO表空間用於存放UNDO數據,當執行DML操作(INSERT,UPDATE和DELETE)時,oracle會將這些操作的舊數據寫入到UNDO段,在oracle9i之前,管理UNDO數據時使用(Rollback Segment)完成的.從oracle9i開始,管理UNDO數據不僅可以使用回滾段,還可以使用UNDO表空間.因為規劃和管理回滾段比較復雜,所有oracle database 10g已經完全丟棄用回滾段.並且使用UNDO表空間來管理UNDO數據。
1、查看系統磁盤狀態
AIX系統:/> df -g (Linux系統: df -h)
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/undolv 30.00 0.00 100% 9 1% /u01/app/u01/app/oracle/undo
2、查看Oracle數據庫表空間的占有率
select a.tablespace_name,
round((a.maxbytes / 1024 / 1024), 2) "sum MB",
round((a.bytes / 1024 / 1024), 2) "datafile MB",
round(((a.bytes - b.bytes) / 1024 / 1024), 2) "used MB",
round(( (a.maxbytes-a.bytes+b.bytes) / 1024 / 1024), 2) "free MB",
round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes,sum(maxbytes) maxbytes
from dba_data_files where maxbytes!=0
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.maxbytes) desc
Tablespace_name SumDatafile(MB) Datafile Used Free Precent_used
1 UNDOTBS1 32767.98 30000 29968 2799.98 91.46
或者通過如下腳本檢查數據庫表空間占用空間情況:
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_data_files group by tablespace_name
union all
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_temp_files group by tablespace_name order by GB;
3、找出UNDO表空間的路徑及大小
SQL> select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name like 'UNDOTBS1'
/u01/app/oracle/undo/undotbs01.dbf 30000
4、檢查UNDO Segment狀態
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
1 0 0 0.000358582 0.000358582 0
2 14 0 0.796791077 0.796791077 735
3 13 0 0.800453186 0.800453186 894
4 12 0 0.805213928 0.805213928 728
5 15 0 1.186126709 1.186126709 922
6 1 0 1.723365784 1.963180542 946
7 3 0 1.732704163 1.977462769 1051
8 5 0 1.978370667 2.228370667 654
9 2 0 2.032501221 2.034454346 707
10 4 0 2.065216064 2.318145752 875
11 11 0 2.100006104 2.100006104 1269
12 8 0 2.630340576 2.700653076 897
13 6 0 2.740814209 2.740814209 1030
14 9 0 2.745697021 2.772064209 1037
15 7 0 2.833526611 2.833526611 1033
16 10 0 3.088363647 3.310592651 989
這還原表空間中還存在16個回滾的對象。
5、創建新的臨時UNDO表空間
可以在其它的磁盤空間臨時創建還原表空間
SQL>
create undo tablespace undotbs2
datafile '/u01/app/oracle/pub/undotbs02.dbf'
size 10M autoextend on;
Tablespace created.
6、切換UNDO表空間為新的UNDO表空間
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
7、驗證當前數據庫的還原表空間
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
8、等待原UNDO表空間所有UNDO SEGMENT OFFLINE
select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
1 SYSTEM SYSTEM 0 ONLINE
2 _SYSSMU1$ UNDOTBS1 1 OFFLINE
3 _SYSSMU2$ UNDOTBS1 2 OFFLINE
48 _SYSSMU47$ UNDOTBS1 47 OFFLINE
49 _SYSSMU48$ UNDOTBS1 48 OFFLINE
50 _SYSSMU49$ UNDOTBS1 49 OFFLINE
51 _SYSSMU50$ UNDOTBS1 50 OFFLINE
52 _SYSSMU51$ UNDOTBS1 51 OFFLINE
53 _SYSSMU52$ UNDOTBS1 52 OFFLINE
54 _SYSSMU53$ UNDOTBS1 53 OFFLINE
55 _SYSSMU54$ UNDOTBS1 54 OFFLINE
56 _SYSSMU55$ UNDOTBS1 55 OFFLINE
57 _SYSSMU56$ UNDOTBS1 56 OFFLINE
58 _SYSSMU57$ UNDOTBS1 57 OFFLINE
59 _SYSSMU58$ UNDOTBS1 58 OFFLINE
60 _SYSSMU59$ UNDOTBS1 59 OFFLINE
61 _SYSSMU60$ UNDOTBS1 60 OFFLINE
62 _SYSSMU61$ UNDOTBS1 61 OFFLINE
63 _SYSSMU62$ UNDOTBS2 62 ONLINE
64 _SYSSMU63$ UNDOTBS2 63 ONLINE
65 _SYSSMU64$ UNDOTBS2 64 ONLINE
66 _SYSSMU65$ UNDOTBS2 65 ONLINE
67 _SYSSMU66$ UNDOTBS2 66 ONLINE
68 _SYSSMU67$ UNDOTBS2 67 ONLINE
69 _SYSSMU68$ UNDOTBS2 68 ONLINE
上面對應的UNDOTBS1還原表空間所對應的回滾段均為OFFLINE
9、刪除原UNDO表空間
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
10、可以再次查看系統磁盤空間：
AIX系統:/> df -g (Linux系統: df -h)
如果需要規范數據庫的表空間和路徑，還原表空間名稱undotbs1和路徑不能改變，
可以安裝剛才的步驟進行切換回來。
1、創建新的原來的UNDO表空間
可以在其它的磁盤空間臨時創建還原表空間
SQL>
create undo tablespace undotbs1
datafile '/u01/app/oracle/undo/undotbs01.dbf'
size 10M autoextend on maxsize 15G;
剛開始為10M，設置自動擴展，最大為15GB
Tablespace created.
2、切換UNDO表空間為新的UNDO表空間
SQL> alter system set undo_tablespace=undotbs1 scope=both;
System altered.
3、驗證當前數據庫的還原表空間
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
4、等待原UNDO表空間所有UNDO SEGMENT OFFLINE
select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
上面對應的UNDOTBS2還原表空間所對應的回滾段均為OFFLINE
5、刪除UNDO2表空間
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
6、可以再次查看系統磁盤空間：
AIX系統:/> df -g (Linux系統: df -h)
undo_retention:指定事物commit後undo 將要保存的時間(秒)，在ORACLE10g中默認的是900秒。
GUARANTEE ： 保證undo_retention參數所設定的時間有效，這個是10g的新功能。
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
在沒有guarantee的保證下，ORACLE並不能保證能夠將undo信息存儲900秒，如果undo表空間不足，那麼ORACLE將忽略undo_retention的設置，直接覆蓋掉以前的undo,這個時候有可能會產生ORA-01555錯誤。如果undo表空間空間足夠，那麼undo將會保存很長一段時間，直到undo表空間達到maxsize，這個時候才會覆蓋undo信息，而且ORACLE會從最古老的undo信息開始覆蓋。
ORACLE推薦我們將undo 表空間中的datafile 設定MAXSIZE ，不要讓它一直自動擴展，如果ORACLE獲得了自動擴展的能力，那麼舊的undo不會被覆蓋，到後來undo表空間會越來越大，越來越大，直到將磁盤空間耗盡。
在有guarantee的保證下，ORACLE將會保證undo信息能夠保存到undo_retention設定的值之後才被覆蓋，如果這個時候同時執行了很多事物，將undo表空間耗完了，那麼那個事物會失敗，會報ORA-30036 錯誤，所以使用guarantee一定要慎用，如果非要使用guarantee，那麼盡量將undo 表空間設大 一點。
Oracle10g開始，如果你設置UNDO_RETENTION為0，那麼Oracle啟用自動調整以滿足最長運行查詢的需要。當然如果空間不足，那麼Oracle滿足最大允許的長時間查詢，而不再需要用戶手工調整。
