收缩Oracle数据文件

      最近有网友提到收缩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  

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏前端黑板报

MySQL之什么是MySQL

这篇教程是帮助你回答:什么是MySQL?以及告诉你为何 MySQL 是世界上最流行的开源数据库的原因。

2984
来自专栏数据和云

SQL审核:OR展开与子查询优化案例详解

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

3704
来自专栏数据和云

【云和恩墨大讲堂】复合索引与绑定变量

讲师简介 ? 邓秋爽(小鱼) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 今晚的...

3616
来自专栏杂烩

Mycat基准测试报告 原

好久没上OSC,上面安排测下Mycat,于是申请服务器,花了两个周做出这个东西,供以借鉴。

2101
来自专栏资深Tester

数据库使用经验分享

2225
来自专栏杨建荣的学习笔记

merge语句导致的性能问题紧急优化 (r9笔记第85天)

晚上正在休息的时候,突然收到一封报警邮件。 报警内容: CPU utilization is too high ------------------------...

2825
来自专栏杨建荣的学习笔记

持续近7个小时的索引扫描的查询优化分析 (r5笔记第44天)

昨天客户的DBA反映有一个数据抽取的任务持续了很长时间最后超时退出了,让我看看有什么地方可以调优一下。 找到了对应的日志,发现在一个大表抽取的时候,抽取持续了将...

4135
来自专栏数据和云

DBA入门之路:察微知渐细致入微

在DBA的职业生涯中,要面临无数的艰难险阻、排忧解难,所以细致入微,严谨认真的风格必不可少。养成了察微知渐的习惯,才能在分析诊断故障时层剖缕析,直指核心;而我也...

2123
来自专栏PPV课数据科学社区

【一文打尽】SQL 数据分析常用语句.....收藏

• 1 基础查询 • 2 字符串\数字\日期时间 • 3 聚合数据查询 • 4 子查询 • 5 联接\组合查询 • 6 高级查询 • 7 更新数据 阅读提醒:点...

40910
来自专栏乐沙弥的世界

数据库三范式

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。

1025

扫码关注云+社区

领取腾讯云代金券