收缩Oracle数据文件

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介:       最近有网友提到收缩Oracle数据文件的问题,这是DBA经常碰到的一个常见问题。通常我们需要收缩相应的数据文件以减少来自磁盘空间的压力以及提高数据库的整体性能。

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

 

Oracle 牛鹏社

更多参考:
  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 备份恢复方案(三)

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
24天前
|
存储 Oracle 关系型数据库
【赵渝强老师】Oracle的还原数据
Oracle数据库中的还原数据(也称为undo数据或撤销数据)存储在还原表空间中,主要用于支持查询的一致性读取、实现闪回技术和恢复失败的事务。文章通过示例详细介绍了还原数据的工作原理和应用场景。
【赵渝强老师】Oracle的还原数据
|
24天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的控制文件与归档日志文件
本文介绍了Oracle数据库中的控制文件和归档日志文件。控制文件记录了数据库的物理结构信息,如数据库名、数据文件和联机日志文件的位置等。为了保护数据库,通常会进行控制文件的多路复用。归档日志文件是联机重做日志文件的副本,用于记录数据库的变更历史。文章还提供了相关SQL语句,帮助查看和设置数据库的日志模式。
【赵渝强老师】Oracle的控制文件与归档日志文件
|
4月前
|
SQL 运维 Oracle
【迁移秘籍揭晓】ADB如何助你一臂之力,轻松玩转Oracle至ADB的数据大转移?
【8月更文挑战第27天】ADB(Autonomous Database)是由甲骨文公司推出的自动化的数据库服务,它极大简化了数据库的运维工作。在从传统Oracle数据库升级至ADB的过程中,数据迁移至关重要。
73 0
|
4月前
|
数据采集 Oracle 关系型数据库
实时计算 Flink版产品使用问题之怎么实现从Oracle数据库读取多个表并将数据写入到Iceberg表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
24天前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle的参数文件与告警日志文件
本文介绍了Oracle数据库的参数文件和告警日志文件。参数文件分为初始化参数文件(PFile)和服务器端参数文件(SPFile),在数据库启动时读取并分配资源。告警日志文件记录了数据库的重要活动、错误和警告信息,帮助诊断问题。文中还提供了相关视频讲解和示例代码。
|
24天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的联机重做日志文件与数据写入过程
在Oracle数据库中,联机重做日志文件记录了数据库的变化,用于实例恢复。每个数据库有多组联机重做日志,每组建议至少有两个成员。通过SQL语句可查看日志文件信息。视频讲解和示意图进一步解释了这一过程。
|
24天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的数据文件
在Oracle数据库中,数据库由多个表空间组成,每个表空间包含多个数据文件。数据文件存储实际的数据库数据。查询时,如果内存中没有所需数据,Oracle会从数据文件中读取并加载到内存。可通过SQL语句查看和管理数据文件。附有视频讲解及示例。
|
3月前
|
Oracle 关系型数据库 数据库
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
打开oracle数据库报错“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。 数据库没有备份,无法通过备份去恢复数据库。用户方联系北亚企安数据恢复中心并提供Oracle_Home目录中的所有文件,急需恢复zxfg用户下的数据。 出现“system01.dbf需要更多的恢复来保持一致性”这个报错的原因可能是控制文件损坏、数据文件损坏,数据文件与控制文件的SCN不一致等。数据库恢复工程师对数据库文件进一步检测、分析后,发现sysaux01.dbf文件损坏,有坏块。 修复并启动数据库后仍然有许多查询报错,export和data pump工具使用报错。从数据库层面无法修复数据库。
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
|
2月前
|
Oracle 关系型数据库 数据库
oracle数据恢复—Oracle数据库文件损坏导致数据库打不开的数据恢复案例
打开oracle数据库时报错,报错信息:“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。急需恢复zxfg用户下的数据。 出现上述报错的原因有:控制文件损坏、数据文件损坏、数据文件与控制文件的SCN不一致等。数据恢复工程师对数据库文件做进一步检测分析后发现sysaux01.dbf文件有坏块。修复sysaux01.dbf文件,启动数据库依然有许多查询报错。export和data pump工具无法使用,查询告警日志并分析报错,确认发生上述错误的原因就是sysaux01.dbf文件损坏。由于该文件损坏,从数据库层面无法修复数据库。由于system和用户表空间的数据文件是正常的,
|
2月前
|
Oracle 关系型数据库 数据库
oracle数据创建同义词
oracle数据创建同义词
51 0