經過之前的大資料量測試，目前系統中的undo表空間被撐大到近20G。釋放其佔用的空間的工作擺在案頭。透過此文將此項維護工作做一個簡單梳理，供大家參考。
思路很簡單，就是透過啟用一個新的undo表空間，然後刪除原有表空間的方式來完成。

1.查詢當前的undo表空間的大小，及系統可用空間大小
sys@ora10g> col file_name for a40
sys@ora10g> select file_name,bytes/1024/1024 MB,tablespace_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME                                        MB TABLESPACE_NAME
---------------------------------------- ---------- ------------------
/oracle/oradata/ora10g/undotbs01.dbf          19870 UNDOTBS1

sys@ora10g> !df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      234G 196G   27G 89% /
/dev/cciss/c0d0p1      99M   13M   82M 13% /boot
tmpfs                  16G     0   16G   0% /dev/shm

2.建立一個新undo表空間
sys@ora10g> create undo tablespace UNDO_TBS1 datafile '/oracle/oradata/ora10g/undo_tbs01.dbf' size 100m;

Tablespace created.

sys@ora10g> select file_name,bytes/1024/1024 MB,tablespace_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME                                        MB TABLESPACE_NAME
---------------------------------------- ---------- ------------------
/oracle/oradata/ora10g/undotbs01.dbf          19870 UNDOTBS1
/oracle/oradata/ora10g/undo_tbs01.dbf           100 UNDO_TBS1

3.將預設的undo表空間設定調整到UNDO_TBS1
sys@ora10g> alter system set undo_tablespace=UNDO_TBS1;

System altered.

4.刪除原有的undo表空間
sys@ora10g> !ls -l /oracle/oradata/ora10g/undotbs01.dbf
-rw-r----- 1 oracle oinstall 20835213312 Nov 23 17:10 /oracle/oradata/ora10g/undotbs01.dbf

sys@ora10g> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

sys@ora10g> !ls -l /oracle/oradata/ora10g/undotbs01.dbf
ls: /oracle/oradata/ora10g/undotbs01.dbf: No such file or directory

5.最後確認一下undo表空間的設定情況
sys@ora10g> col file_name for a40
sys@ora10g> select file_name,bytes/1024/1024 MB,tablespace_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME                                        MB TABLESPACE_NAME
---------------------------------------- ---------- ------------------
/oracle/oradata/ora10g/undo_tbs01.dbf           100 UNDO_TBS1

sys@ora10g> show parameter undo_tablespace

NAME                TYPE          VALUE
------------------- ------------- -------------------
undo_tablespace     string        UNDO_TBS1

6.如果不想等待N長時間才看到被釋放出來的空間，此時需要重新啟動一下資料庫。
1）檢視此時的系統可用空間，結論是被釋放的空間沒有歸還給作業系統
sys@ora10g> !df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      234G 196G   27G 89% /
/dev/cciss/c0d0p1      99M   13M   82M 13% /boot
tmpfs                  16G     0   16G   0% /dev/shm

2）重新啟動資料庫
sys@ora10g> startup force;
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  2080320 bytes
Variable Size             905970112 bytes
Database Buffers         4445962240 bytes
Redo Buffers               14696448 bytes
Database mounted.
Database opened.

3）在此確認，此時空間已經可以被成功釋放
sys@ora10g> !df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      234G 177G   46G 80% /
/dev/cciss/c0d0p1      99M   13M   82M 13% /boot
tmpfs                  16G     0   16G   0% /dev/shm

7.小結
使用上面的方法可以釋放過分增長的undo表空間，但是根本上的解決方法是防止這種現象的發生。可以考慮採用限制undo表空間最大值的方法來緩解此類事件的發生。
sys@ora10g> alter database datafile '/oracle/oradata/ora10g/undotbs01.dbf' autoextend on next 10m maxsize 1g;

Database altered.

Good luck.

secooler
09.11.23

-- The End --

【UNDO】使用重建UNDO表空間方法解決UNDO表空間過大問題

相關文章