【UNDO】使用重建UNDO表空间方法解决UNDO表空间过大问题

经过之前的大数据量测试，目前系统中的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 --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-620336/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-620336/