这是公司一套10g RAC双节点数据库，近期业务量增长比较大，导致硬盘空间有些吃紧，在新的存储添加之前。我想到的是收缩表空间，缓解存储。做一简单记录如下：
① 操作系统版本：

[root@oracle-rac2 bin]# su - oracle
[oracle@oracle-rac2 ~]$ uname -a
Linux oracle-rac2.gillion.com.cn 2.6.18-194.0.0.0.3.el5 #1 SMP Mon Mar 29 18:14:09 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oracle-rac2 ~]$ uname -r
2.6.18-194.0.0.0.3.el5

②数据库版本：

[oracle@oracle-rac2 ~]$ . oraenv
ORACLE_SID = [glndb2] ? glndb
[oracle@oracle-rac2 ~]$ export ORACLE_SID=glndb2
[oracle@oracle-rac2 ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 25 09:32:03 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL>

③ 采用ASM存储，收缩表空间之前，存储信息如下：

SQL> select name,block_size,state,type,total_mb,free_mb from v$asm_diskgroup;

NAME                           BLOCK_SIZE STATE       TYPE     TOTAL_MB    FREE_MB
------------------------------ ---------- ----------- ------ ---------- ----------
ORADATA                              4096 MOUNTED     EXTERN     262138      61364

SQL> select mount_status,header_status ,state,redundancy ,total_mb,free_mb ,name ,path from v$asm_disk;

MOUNT_S HEADER_STATU STATE    REDUNDA   TOTAL_MB    FREE_MB NAME                           PATH
------- ------------ -------- ------- ---------- ---------- ------------------------------ ----------
CACHED  MEMBER       NORMAL   UNKNOWN     262138      61364 ORADATA_0000                   /dev/oradata1

④ 表空间FR7_SA_DATA在收缩之前占用的空间如下：

SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files where tablespace_name='FR7SA_DATA';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                         M
------------------------------ ----------
+ORADATA/glndb/datafile/fr7sa_data.425.751556425
FR7SA_DATA                           4996
接下来，采用如下命令resize该表空间下的数据文件：
SQL> alter database datafile '+ORADATA/glndb/datafile/fr7sa_data.425.751556425' resize 3325M;

Database altered.
收缩之后，该数据文件大小为3325M，大约释放了1670M的空间。
SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files where tablespace_name='FR7SA_DATA';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME                         M
------------------------------ ----------
+ORADATA/glndb/datafile/fr7sa_data.425.751556425
FR7SA_DATA                           3325

SQL>

⑤ 最后，再查看ASM磁盘、磁盘组的存储信息：

SQL>select name,block_size,state,type,total_mb,free_mb from v$asm_diskgroup;

NAME                           BLOCK_SIZE STATE       TYPE     TOTAL_MB    FREE_MB
------------------------------ ---------- ----------- ------ ---------- ----------
ORADATA                              4096 MOUNTED     EXTERN     262138      63035

SQL>select mount_status,header_status ,state,redundancy ,total_mb,free_mb ,name ,path from v$asm_disk;

MOUNT_S HEADER_STATU STATE    REDUNDA   TOTAL_MB    FREE_MB NAME                           PATH
------- ------------ -------- ------- ---------- ---------- ------------------------------ ----------
CACHED  MEMBER       NORMAL   UNKNOWN     262138      63035 ORADATA_0000                   /dev/oradata1

⑥ 至此，存储回收了大概1.6G。附上：收缩表空间的脚本。

col file# for 999
col name for a50
col resizecmd for a100

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
       ceil(HWM * a.block_size)/1024/1024 ResizeTo,
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
       'alter database datafile '''||a.name||''' resize '||
       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
     (select file_id,max(block_id+blocks-1) HWM
       from dba_extents
       group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5

如果只是想对某个表个间的datafile resize,可采用:
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
       ceil(HWM * a.block_size)/1024/1024 ResizeTo,
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
       'alter database datafile '''||a.name||''' resize '||
       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
     (select file_id,max(block_id+blocks-1) HWM
       from dba_extents where file_id in
              (select b.file#  From v$tablespace a ,v$datafile b
                where a.ts#=b.ts# and a.name='MP2000')
       group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5

计划tempfile可以resize的空间.on apply that have only one tempfile

select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
     (select tmsize.maxblk*bk.value/1024/1024 siz from
         (select nvl(max(segblk#),128) maxblk  from v$sort_usage) tmsize,
         (select value From v$parameter where name = 'db_block_size') bk) b
生产环境，需慎重使用上述脚本。