Chinaunix首页 | 论坛 | 博客
  • 博客访问: 468153
  • 博文数量: 178
  • 博客积分: 2547
  • 博客等级: 少校
  • 技术积分: 1764
  • 用 户 组: 普通用户
  • 注册时间: 2006-02-22 08:27
文章分类

全部博文(178)

文章存档

2014年(2)

2013年(2)

2012年(2)

2010年(19)

2009年(26)

2008年(69)

2007年(20)

2006年(38)

我的朋友

分类: Oracle

2008-11-27 22:22:44

如何Shrink Undo表空间,释放过度占用的空间

来自:
环境:
OS:Red Hat Enterprise Linux AS release 4 (Nahant)
DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

一台Oracle10gR2数据库报出如下错误:
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX
登陆检查,发现是SYSAUX表空间空间用尽,不能扩展,尝试手工扩展表空间:
alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m
Tue Nov 29 23:31:38 2005
ORA-1237 signalled during: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m...
出现ORA-1237错误,提示空间不足。这时候我才认识到是磁盘空间可能被用完了.

是谁"偷偷的"用了那么多空间呢(本来有几十个G的Free磁盘空间的)?
检查数据库表空间占用空间情况:
SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB
2 from dba_data_files group by tablespace_name
3 union all
4 select tablespace_name,sum(bytes)/1024/1024/1024 GB
5 from dba_temp_files group by tablespace_name order by GB;

TABLESPACE_NAME            GB
------------------------------ ----------
USERS             .004882813
UNDOTBS2            .09765625
SYSTEM             .478515625
SYSAUX             .634765625
WAPCM_TS_VISIT_DETAIL      .9765625
HY_DS_DEFAULT              1
MINT_TS_DEFAULT             1
MMS_TS_DATA2            1.375
MMS_IDX_SJH               2
MMS_TS_DEFAULT             2
IVRCN_TS_DATA              2

TABLESPACE_NAME            GB
------------------------------ ----------
MMS_TS_DATA1              2
CM_TS_DEFAULT              5
TEMP              20.5498047
UNDOTBS1            27.1582031

15 rows selected.
不幸的发现,UNDO表空间已经扩展至27G,而TEMP表空间也扩展至20G,这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。
显然曾经有大事务占用了大量的UNDO表空间和Temp表空间,Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性).
现在我们可以采用如下步骤回收UNDO空间:

1.确认文件
SQL> select file_name,bytes/1024/1024 from dba_data_files
2 where tablespace_name like 'UNDOTBS1';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
+ORADG/danaly/datafile/undotbs1.265.600173875
     27810
2.检查UNDO Segment状态
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;

    USN   XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024  SHRINKS
---------- ---------- --------------------- ---------------------- ----------
     0     0      .000358582       .000358582     0
     2     0      .071517944       .071517944     0
     3     0       .13722229       .13722229     0
     9     0      .236984253       .236984253     0
    10     0      .625144958       .625144958     0
     5     1      1.22946167       1.22946167     0
     8     0      1.27175903       1.27175903     0
     4     1      1.27895355       1.27895355     0
     7     0      1.56770325       1.56770325     0
     1     0      2.02474976       2.02474976     0
     6     0       2.9671936       2.9671936     0

11 rows selected.
3.创建新的UNDO表空间
SQL> create undo tablespace undotbs2 ;
(经测试,在9i环境下后面还要加上datafile '/opt/..../undotbs2.dbf' size 1024M)
Tablespace created.
4.切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

创建了新的UNDO表空间以后,如果不知道系统使用的是pfile还是spfile文件,应使用参数both,会同时修改spfile文件,避免出现冲突。

5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;


    USN   XACTS STATUS     RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024  SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
    14     0 ONLINE           .000114441       .000114441     0
    19     0 ONLINE           .000114441       .000114441     0
    11     0 ONLINE           .000114441       .000114441     0
    12     0 ONLINE           .000114441       .000114441     0
    13     0 ONLINE           .000114441       .000114441     0
    20     0 ONLINE           .000114441       .000114441     0
    15     1 ONLINE           .000114441       .000114441     0
    16     0 ONLINE           .000114441       .000114441     0
    17     0 ONLINE           .000114441       .000114441     0
    18     0 ONLINE           .000114441       .000114441     0
     0     0 ONLINE           .000358582       .000358582     0

    USN   XACTS STATUS     RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024  SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
     6     0 PENDING OFFLINE       2.9671936       2.9671936     0

12 rows selected.
再看:
11:32:11 SQL> /

    USN   XACTS STATUS     RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024  SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
    15     1 ONLINE           .000114441       .000114441     0
    11     0 ONLINE           .000114441       .000114441     0
    12     0 ONLINE           .000114441       .000114441     0
    13     0 ONLINE           .000114441       .000114441     0
    14     0 ONLINE           .000114441       .000114441     0
    20     0 ONLINE           .000114441       .000114441     0
    16     0 ONLINE           .000114441       .000114441     0
    17     0 ONLINE           .000114441       .000114441     0
    18     0 ONLINE           .000114441       .000114441     0
    19     0 ONLINE           .000114441       .000114441     0
     0     0 ONLINE           .000358582       .000358582     0

11 rows selected.

Elapsed: 00:00:00.00
6.删除原UNDO表空间
11:34:00 SQL> drop tablespace undotbs1 including contents;
(本人经测试觉得其实是否可以用drop tablespace undotbs1 including contents and datafiles;直接连硬盘里面的dbf文件件一起删除)
Tablespace dropped.

Elapsed: 00:00:03.13
7.检查空间情况
由于我使用的ASM管理,可以使用10gR2提供的信工具来察看空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB   Mirror_used_MB
21625        21625
ASMCMD> exit

空间已经释放。
阅读(584) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~