Shrink Undo Tablespace

-----------------------------------------------------
收缩undo tbs
-----------------------------------------------------

1.检查数据库表空间占用空间情况:

SQL> 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;[@more@]

TABLESPACE_NAME GB
------------------------------ ----------
SMPERP_MV_IDX .48828125
SYSAUX 1
SMPERP_MV 10.4882813
SYSTEM 15
TEMP 30
UNDOTBS2 51.9944458
USERS 571.992188

2.查看undo datafile

SQL> select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name like 'UNDOTBS2';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
/oradata/STCSMES/undotbs01.dbf
32762.3125

/oradata/STCSMES/undotbs02.dbf
5120

/oradata/STCSMES/undotbs13.dbf
5120


FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
/oradata/STCSMES/undotbs03.dbf
5120

/oradata/STCSMES/undotbs04.dbf
5120
3.查看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
---------- ---------- --------------------- ---------------------- ----------
6 0 .000175476 .000175476 0
10 0 .000175476 .000175476 0
2 0 .000236511 .000236511 0
1 0 .000236511 .000236511 0
11 0 .000236511 .000236511 0
7 0 .000297546 .000297546 0
0 0 .000358582 .000358582 0
13 0 .001091003 .001091003 0
8 0 .001091003 .001091003 0
12 0 .001091003 .001091003 0
4 0 .001091003 .001091003 0

USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
3 0 .001091003 .001091003 0
5 0 .001091003 .001091003 0
9 0 .002067566 .002067566 0

14 rows selected.

4.创建新的UNDO表空间

SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE
'/oradata/STCSMES/undotbs05.dbf' SIZE 5120m AUTOEXTEND ON NEXT 8K;

Tablespace created.

5.切换UNDO表空间为新的UNDO表空间

SQL> alter system set undo_tablespace=undotbs1;

System altered.

6.查看UNDO表空间,如果等待原UNDO表空间还在UNDO SEGMENT OFFLINE，请等待

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
from v$rollstat order by rssize;

USN XACTS STATUS RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024 SHRINKS
---------------------- ----------
46 0 ONLINE .000114441
.000114441 0

41 0 ONLINE .000114441
.000114441 0

42 0 ONLINE .000114441
.000114441 0


USN XACTS STATUS RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024 SHRINKS
---------------------- ----------
43 0 ONLINE .000114441
.000114441 0

44 0 ONLINE .000114441
.000114441 0

45 0 ONLINE .000114441
.000114441 0


USN XACTS STATUS RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024 SHRINKS
---------------------- ----------
53 0 ONLINE .000114441
.000114441 0

47 0 ONLINE .000114441
.000114441 0

48 0 ONLINE .000114441
.000114441 0


USN XACTS STATUS RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024 SHRINKS
---------------------- ----------
49 0 ONLINE .000114441
.000114441 0

50 0 ONLINE .000114441
.000114441 0

51 0 ONLINE .000114441
.000114441 0


USN XACTS STATUS RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024 SHRINKS
---------------------- ----------
52 0 ONLINE .000114441
.000114441 0

0 0 ONLINE .000358582
.000358582 0


14 rows selected.

7.删除原UNDO表空间及数据文件。

SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

8.查看是否还有在UNDOTBS2表空间上online状态的回滚段

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
2 where tablespace_name='UNDOTBS2' and status = 'ONLINE';

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU52$ PUBLIC UNDOTBS2 ONLINE

9.通过alert log查看，undo表空间还有活动事务, 还在迁移中

Thu Sep 2 17:01:53 2010
Undo Tablespace 1 moved to Pending Switch-Out state.

10.undo表空间迁移完毕

Thu Sep 2 20:50:24 2010
Undo Tablespace 1 successfully switched out.

11.再次drop原undo space

SQL> drop tablespace undotbs1 including contents and datafiles cascade constraints;

Tablespace dropped.

12.查看表空间使用状况

SQL> 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;

TABLESPACE_NAME GB
------------------------------ ----------
SMPERP_MV_IDX .391601563
SYSAUX .745117188
SMPERP_MV 2.58691406
UNDOTBS1 5
SYSTEM 13.9882813

13.查看磁带空间是否释放
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 60G 16G 42G 27% /
/dev/sdb1 99G 2.6G 91G 3% /archivelog
/dev/sda1 99M 14M 81M 15% /boot
none 2.0G 0 2.0G 0% /dev/shm
/dev/sdb2 727G 680G 11G 99% /oradata

$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 60G 16G 42G 27% /
/dev/sdb1 99G 3.4G 91G 4% /archivelog
/dev/sda1 99M 14M 81M 15% /boot
none 2.0G 0 2.0G 0% /dev/shm
/dev/sdb2 727G 628G 63G 91% /oradata

参考网址：

http://www.eygle.com/archives/2005/11/oracle_howto_shrink_undo_tablespace.html

http://hi.baidu.com/edeed/blog/item/de4dc93d823e25ce9e3d625b.html

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

转载于:http://blog.itpub.net/789833/viewspace-1037359/