是谁"偷偷的"用了那么多空间呢(本来有几十个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诊断案例-Spfiletb案例一则
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
7.检查空间情况
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看tb空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB Mirror_used_MB
21625 21625
ASMCMD> exit
空间已经释放。
.item-area{width:578px;margin:15px auto;border-top:1px solid #ddd;color:#666}
.item-area a,.item-area a:link,.item-area a:visited{color:#666;text-decoration:none}
.item-area a:hover{color:#3a7ad9;text-decoration:underline;}
a img{border:none;vertical-align:middle}
.item-area h2,.item-area h3{float:none;font-size:100%;font-weight:normal;}
.item-area .h2{height:25px;margin:10px 0;padding-left:35px;*float:left;font:bold 14px/25px "宋体";background:url(http://sns.thea.cn/module/images/icos.png) no-repeat 0 0}
.item-area span.more{float:right;font:normal 12px/25px "宋体"}
.item-area a.more{float:right;font:normal 12px/25px "宋体"}
.item-a{margin-bottom:15px}
.item-a .h-ksrm{background-position:0 0}
.item-a li{*display:inline;overflow:hidden;zoom:1;line-height:2em;padding-left:35px;font-size:14px;background: url(http://sns.thea.cn/module/images/btns.png) no-repeat -1px -28px;}
.item-a li a{float:left;}
.item-a .testBtn{float:right;width:58px;height:21px;line-height:21px;font-size:12px;margin-top:5px;margin-top:3px;text-align:center;background:url(http://sns.thea.cn/module/images/btns.png) no-repeat -1px -1px; color:#FFFFFF;}
.item-a a.freeBtn{width:20px;margin:0 0 0 6px;line-height:28px;color:#fff;font-size:12px;text-indent:-9999px;background: url(http://sns.thea.cn/module/images/icos.png) no-repeat 0 -131px;}
.item-a li.hots a.freeBtn{background-position:0 -105px}
.item-a a.examnum em{font-style:normal;color:red;font-weight:bold;}
.item-b {padding:5px 0 20px;border-top:1px dashed #ddd;border-bottom:1px dashed #ddd}
.xsjl-list-col3s li{display:table-cell;*display:inline;zoom:1;vertical-align:top;width:182px;padding-right:10px;line-height:150%;font-size:12px;}
.item-b .h-xsjl{background-position:0 -26px}
.item-b .pic{float:left;margin:3px 10px 0 0;}
.item-b em{font-style:normal;color:#dc2c2c}
.item-b a.join{display:inline-block;padding-left:20px;background:url(http://sns.thea.cn/module/images/icos.png) no-repeat 0 -160px}
.item-b .xsjl-list-col3s h3 a{display:inline-block;width:120px;overflow:hidden;white-space:nowrap;color:#3a7ad9}
.item-b .xsjl-list-col3s h3{text-align:left;line-height:150%;font-family:"宋体","微软雅黑"}