最近有网友提到收缩Oracle数据文件的问题，这是DBA经常碰到的一个常见问题。通常我们需要收缩相应的数据文件以减少来自磁盘空间的压力以及提高数据库的整体性能。但这并非对于所有情形都是适用的，尤其是生产环境。因为生产环境数据清洗相当较少，因此空间浪费也比较小，而且一旦收缩之后又要重新自动扩展数据文件，浪费系统资源。对于UAT，DEV环境，多DB，磁盘空间压力大的情形，收缩一下非常有必要。勒紧裤带过日子也是常有的事情，哈哈。总之收缩数据文件会使得磁盘空间得以释放以及加快数据迁移，RMAN备份等。本文分享了Tom大师的收缩脚本以及给出了undo，临时表空间，表段收缩的链接。
几种收缩的情形：
收缩表段(shrink space)
收缩临时表空间
收缩undo表空间
1、演示收缩数据文件
robin@ORADB:~/dba_scripts/custom/sql> sql
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 30 15:05:18 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
goex_admin@USBOTST> @shrink_data_files;
VALUE
--------------------
8192
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u02/database/USBOTST/oradata/sysUSBOTST.dbf 605 650 45
/u02/database/USBOTST/oradata/USBOTST_archive_idx. 725 1,871 1,146
dbf
/u02/database/USBOTST/oradata/USBOTST_his_idx.dbf 1 32 31
/u02/database/USBOTST/oradata/USBOTST_ipo_idx.dbf 7 10 3
/u02/database/USBOTST/oradata/USBOTST_account_tbl. 6,293 6,293 0
dbf
/u02/database/USBOTST/oradata/USBOTST_rpt_tbl.dbf 21 373 352
/u02/database/USBOTST/oradata/USBOTST_audit_tbl.db 938 966 28
f
/u02/database/USBOTST/oradata/tbs_rman01.dbf 13 50 37
/u02/database/USBOTST/undo/undotbsUSBOTST.dbf 358 7,350 6,992
/u02/database/USBOTST/oradata/USBOTST_archive_tbl. 760 1,950 1,190
dbf
/u02/database/USBOTST/oradata/USBOTST_rpt_idx.dbf 10 359 349
/u02/database/USBOTST/oradata/USBOTST_vou_tbl.dbf 4 145 141
/u02/database/USBOTST/oradata/USBOTST_stock_l_tbl. 4 20 16
dbf
/u02/database/USBOTST/oradata/USBOTST_ca_idx.dbf 1 22 21
/u02/database/USBOTST/oradata/USBOTST_his_tbl.dbf 1 959 958
/u02/database/USBOTST/oradata/USBOTST_vou_idx.dbf 2 90 88
/u02/database/USBOTST/oradata/sysauxUSBOTST.dbf 697 800 103
/u02/database/USBOTST/oradata/spot_data.dbf 81 95 14
/u02/database/USBOTST/oradata/USBOTST_tx_tbl.dbf 16 103 87
/u02/database/USBOTST/oradata/USBOTST_tx_his_tbl.d 88 878 790
bf
/u02/database/USBOTST/oradata/USBOTST_ca_tbl.dbf 1 60 59
/u02/database/USBOTST/oradata/USBOTST_imp_exp_tbl. 60 108 48
dbf
.........................................................................
--------
sum 29,686 -->可被释放的总空间
44 rows selected.
Database altered.
Database altered.
Database altered.
Database altered.
alter database datafile '/u02/database/USBOTST/oradata/USBOTST_ipo_idx.dbf'
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
---> Author : Leshami --->Blog : http://blog.csdn.net/leshami
...........................................
--可能存在个别文件出现无法收缩的情形，提示超出最小的size。
2、收缩脚本
--此脚本可用于Oracle 10g,11g
robin@ORADB:~/dba_scripts/custom/sql> more shrink_data_files.sql
set verify off
col value format a20
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
set heading off feedback off termout off
spool /tmp/tmp_shrink_data_files.sql
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
spool off;
set heading on feedback on termout on
@/tmp/tmp_shrink_data_files.sql
更多参考:
Linux/Unix shell 脚本跨服务器跨实例执行SQL
Linux/Unix shell 脚本中调用SQL,RMAN脚本
Linux/Unix shell sql 之间传递变量
Linux/Unix shell 参数传递到SQL脚本
Linux/Unix shell 调用 PL/SQL
Linux/Unix shell 监控Oracle实例(monitor instance)
Linux/Unix shell 监控Oracle监听器(monitor listener)
Linux/Unix shell 监控Oracle告警日志(monitor alter log file)
Linux/Unix shell 自动导出Oracle数据库
Linux/Unix shell 自动 FTP 备份档案
Linux/Unix shell 自动导入Oracle数据库
Linux/Unix shell 自动发送AWR report
Linux/Unix shell 自动发送AWR report(二)
Linux/Unix shell 脚本清除归档日志文件
Linux/Unix shell 脚本监控磁盘可用空间
Oracle 测试常用表BIG_TABLE
Oracle 性能相关常用脚本(SQL)
Oracle OWI 等待事件视图(v$session_wait/v$session_event/v$system_event)
Oracle 监控索引的使用率
Linux 下RMAN备份shell脚本
Oracle RMAN 清除归档日志
sqlplus spool 到动态日志文件名
基于catalog 创建RMAN存储脚本
批量迁移Oracle数据文件，日志文件及控制文件
中小型数据库 RMAN CATALOG 备份恢复方案(一)
中小型数据库 RMAN CATALOG 备份恢复方案(二)
中小型数据库 RMAN CATALOG 备份恢复方案(三)