« One Story About Love | Blog首页 | Oracle HowTo:如何通过RMAN进行裸设备和文件系统之间的数据文件迁移 »
Oracle HowTo:如何Shrink Undo表空间,释放过度占用的空间
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2005/11/oracle_howto_shrink_undo_tablespace.html
环境:
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表空间空间用尽,不能扩展,尝试手工扩展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; Tablespace created. |
4.切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both; System altered. |
此处使用spfile需要注意，以前曾经记录过这样一个案例:Oracle诊断案例-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; Tablespace dropped. Elapsed: 00:00:03.13 |
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM [oracle@danaly ~]$ asmcmd ASMCMD> du Used_MB Mirror_used_MB 21625 21625 ASMCMD> exit |
空间已经释放。
-----
这篇 【Oracle HowTo:如何Shrink Undo表空间,释放过度占用的空间】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
By eygle on 2005-11-30 11:28 | Comments (12) | Posted to HowTo | Edit |Pageviews:
Listed below are links to weblogs that reference Oracle HowTo:如何Shrink Undo表空间,释放过度占用的空间:
» Oracle HowTo: How to deal with Ora-600 4193 error from Friends Life and Oracle
在解决2662错误之后，经常会出现Ora-00600 4193错误，经常可以在alert文件中看到的错误号类似 [Read More]
Tracked on September 7, 2006 1:56 PM
留言 (12)
曾经这么做过，没有问题。
不过，这个数据库得undo为什么会这么大呢？undo应该会自动shrink到optimal大小的啊。
tmp表空间如果设置了循环使用是不是就不会变得这么大了？我一般会设置tmp有一个最大值，然后循环使用，不知道是否合理。
Posted by: wangf at November 30, 2005 9:15 PM
大,是因为有大事务的原因!
而且Oracle不能自动Shrink的情况太多了.
Posted by: eygle at December 1, 2005 4:20 PM
提个问题，让你完善你的文章，呵呵。
加入磁盘空间现在已经不允许再创建一个新的undo表空间了呢？
Posted by: kamus at December 2, 2005 12:15 AM
我这个就没空间了的!
随便把一个数据表空间resize缩小了100M,就行了...
:D
Posted by: eygle at December 2, 2005 12:20 AM
问一下：要是UNDO一直被占用怎么办？
Posted by: loveym at December 13, 2005 3:15 PM
等...
Posted by: eygle at December 13, 2005 3:17 PM
我一般都会设定一个特定大小的UNDO，关键是在需求test时候把握好。我想正式上线后是不会发生这种事情滴。或者也会给UNDO一个最大值！:)
Posted by: afly at January 3, 2006 5:49 PM
hehe
应该顺手把temp也干掉……
Posted by: rollingpig at January 6, 2006 2:03 PM
temp给resize了,没写进来:)
Posted by: eygle at January 6, 2006 2:29 PM
我的数据库有1.5T大小，经常需要一些两个大表的连接（一个表将近100G），temp扩到了140G还是提示不够，怎么办？eygle请给点建议
Posted by: leeecho at February 17, 2006 9:59 AM
通常的方法是,优化相关的SQL,调整PGA以优化排序
如果是DSS系统,类似情况还是常见的.
Posted by: eygle at February 17, 2006 10:05 AM
怎么看undotbs1已经不再使用了啊，看哪个字段的。
Posted by: seamanczg1984 at November 15, 2007 10:28 PM
