这是公司一套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
生产环境，需慎重使用上述脚本。