Oracle表空间数据库文件收缩案例解析

我们经常会遇到数据库磁盘空间爆满的问题,或由于归档日志突增、或由于数据文件过多、大导致磁盘使用紧俏。这里主要说的场景是磁盘空间本身很大,但表空间对应的数据文件初始化的时候就直接顶满了磁盘空间,导致经常收到磁盘空间满的报警。

一、错误信息

告警内容如下:

【发现异常】地产客储系统数据库Oracle_192.168.xx.xx,192.168.xx.xx,数据库customer,连接错误,0 ORA-00257: archiver error. Connect internal only, until freed.

【发生时间】2018.07.04 09:12:21

二、错误原因

上述错误一看大致就知道是由于磁盘空间不足,导致归档无法完成所致,我们只需要清理足够的磁盘空间即可。但在磁盘清理的时候发现磁盘空间本身可清理的不多,被很多很大的数据文件占用,而实际使用的segment大小总共不足400G,磁盘空间本身1T,所以我们可以通过收缩数据文件的方式回收磁盘空间。

数据文件初始化方式:

1.我们创建表空间一般有两种方式初始化其数据文件,即指定初始大小为32G(很大的值)或指定初始大小为100M(很小的值)然后通过自动扩展方式慢慢按需增长。

2.第一种初始数据文件方法坏处就是开始不管你用不用到那么大,都会占用这么大的磁盘空间(这种数据迁移的时候可以使用)。第二种初始化方法按需增长,比较好的监控实际使用磁盘空间,所以推荐初始值很小,使用自动扩展慢慢增长的方式。

三、处理步骤

1.查看磁盘空间大小

2.查看数据库表空间大小

#!/bin/bash
sqlplus -S /nolog  <<EOF
conn /as sysdba;
set echo off heading on underline on;
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;
column db_name  heading "DB Name"  new_value db_name  format a12;
column dbid   heading "DB Id"   new_value dbid   format 9999999999 just c;
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid      dbid
   , d.name      db_name
   , i.instance_number inst_num
   , i.instance_name  inst_name
 from v\$database d,
    v\$instance i;
set term on feedback off lines 130 pagesize 999 tab off trims on
column MB format 999,999,999 heading "Total MB"
column free format 9,999,999 heading "Free MB"
column used format 99,999,999 heading "Used MB"
column Largest format 999,999 heading "LrgstMB"
column tablespace_name format a20 heading "Tablespace"
column status format a3 truncated
column max_extents format 99999999999 heading "MaxExt"
col extent_management      for a1 trunc  head "M"
col allocation_type       for a1 trunc  head "A"
col Ext_Size for a4 trunc head "Init"
column pfree format a3 trunc heading "%Fr"
break on report
compute sum of MB on report
compute sum of free on report
compute sum of used on report
select
 d.tablespace_name,
 decode(d.status,
  'ONLINE', 'OLN',
  'READ ONLY', 'R/O',
  d.status) status,
 d.extent_management,
 decode(d.allocation_type,
  'USER','',
  d.allocation_type) allocation_type,
 (case
  when initial_extent < 1048576
  then lpad(round(initial_extent/1024,0),3)||'K'
  else lpad(round(initial_extent/1024/1024,0),3)||'M'
 end) Ext_Size,
 NVL (a.bytes / 1024 / 1024, 0) MB,
 NVL (f.bytes / 1024 / 1024, 0) free,
 (NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)) used,
 NVL (l.large / 1024 / 1024, 0) largest,
 d.MAX_EXTENTS ,
 lpad(round((f.bytes/a.bytes)*100,0),3) pfree,
 (case when round(f.bytes/a.bytes*100,0) >= 20 then ' ' else '*' end) alrt
FROM sys.dba_tablespaces d,
 (SELECT  tablespace_name, SUM(bytes) bytes
  FROM dba_data_files
  GROUP BY tablespace_name) a,
 (SELECT  tablespace_name, SUM(bytes) bytes
  FROM dba_free_space
  GROUP BY tablespace_name) f,
 (SELECT  tablespace_name, MAX(bytes) large
  FROM dba_free_space
  GROUP BY tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
 AND d.tablespace_name = f.tablespace_name(+)
 AND d.tablespace_name = l.tablespace_name(+)
 AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
select
 d.tablespace_name,
 decode(d.status,
  'ONLINE', 'OLN',
  'READ ONLY', 'R/O',
  d.status) status,
 d.extent_management,
 decode(d.allocation_type,
  'UNIFORM','U',
  'SYSTEM','A',
  'USER','',
  d.allocation_type) allocation_type,
 (case
  when initial_extent < 1048576
  then lpad(round(initial_extent/1024,0),3)||'K'
  else lpad(round(initial_extent/1024/1024,0),3)||'M'
 end) Ext_Size,
 NVL (a.bytes / 1024 / 1024, 0) MB,
 (NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)) free,
 NVL (t.bytes / 1024 / 1024, 0) used,
 NVL (l.large / 1024 / 1024, 0) largest,
 d.MAX_EXTENTS ,
 lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) pfree,
 (case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100) >= 20 then ' ' else '*' end) alrt
FROM sys.dba_tablespaces d,
 (SELECT  tablespace_name, SUM(bytes) bytes
  FROM dba_temp_files
  GROUP BY tablespace_name order by tablespace_name) a,
 (SELECT  tablespace_name, SUM(bytes_used ) bytes
  FROM v\$temp_extent_pool
  GROUP BY tablespace_name) t,
 (SELECT  tablespace_name, MAX(bytes_cached) large
  FROM v\$temp_extent_pool
  GROUP BY tablespace_name order by tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
 AND d.tablespace_name = t.tablespace_name(+)
 AND d.tablespace_name = l.tablespace_name(+)
 AND d.extent_management LIKE 'LOCAL'
 AND d.contents LIKE 'TEMPORARY'
 ORDER by 1
/
prompt
exit
EOF

3.查询可直接收缩表空间数据文件

这里查看的是可以直接收缩的数据文件大小,比如最开始初始化的数据文件为32G,在数据文件高水位以下的为20G,那么可直接回收的为12G。

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;

4.直接收缩数据文件

alter database datafile '/oracle/oradata/bi/data01.dbf' resize 1548M;

5.再次查看磁盘空间,已释放很多,可手动完成归档测试。

四、总结

针对oracle的数据文件收缩(磁盘空间收缩),我们一般可通过当前磁盘空间查看(df -h)——>执行可直接收缩的查询命令和收缩命令——>执行大表高水位收缩——>执行表空间高水位收缩(降低文件高水位线)——>再次执行直接回收表空间数据文件命令

直接收缩数据文件的方式参考本文上述步骤即可完成。

那么如何降低表空间的数据文件高水位,进而完成表空间数据文件回收呢?

1.查看大于10G的数据文件

select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024) >10 order by file_id;

2.查看大于10G的数据文件对应的数据块信息

select file_id,max(block_id+blocks-1) HWM,block_id
       from dba_extents
       where file_id =14
       group by file_id,block_id
       order by hwm desc ;

3.查看大表对应的数据块信息

##查看大表
select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024) >10 order by file_id;
##查看大表对应的块
 select owner,segment_name,file_id,block_id,blocks from dba_extents where segment_name='TABLE_NAME';

4.降低表的高水位

alter table table_name move;
alter index idx_name rebuild;

5.查看数据文件对应的最大的block_id

SELECT MAX(block_id)
     FROM dba_extents
     WHERE tablespace_name = 'TABLESPACE_NAME'; 

6.执行数据文件收缩

(block_id+blocks-1)数据文件的HWM
alter database datafile '/oracle/oradata/bi/data01.dbf' resize xxxM;

总结

以上所述是小编给大家介绍的Oracle表空间数据库文件收缩案例解析,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • Oracle数据库中表空间的基本管理操作小结

    DB存储层次结构 (画了个草图,将就看一下...XD) 管理表空间 -system 存放数据字典信息,必须的,创建数据库时第一个创建 -sysaux 10g新,必须的,辅助分担system的负荷,系统管理如oem等三方工具等 -undo 存储回滚段信息,提供事务回滚功能 -temp 存放用户排序的临时数据 -index 存放用户表上的索引信息 -other 不同用户表数据 获取表空间和数据文件信息 表空间信息:DBA_TABLESPACES        V$TABLESPACE        

  • Oracle数据库自带表空间的详细说明

    需求: 需要整理现场用户创建的表空间以及其存储数据,进行规范化管理.在整理用户现场建立的表空间时,需要排除掉非用户创建的表空间,所有首先需要那些表空间是用户创建的,那些是Oracle自带的. 本机测试建立一个新库,发现自带表空间如下: 下面对每一个表空间进行简单说明(部分内容引自网络): 1.SYSAUX表空间 SYSAUX表空间在Oracle Database 10g中引入,作为SYSTEM表空间的辅助表空间.以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建.SYS

  • 在Linux系统上同时监控多个Oracle数据库表空间的方法

    一,设计背景   由于所在公司ORACLE数据库较多,传统人工监控表空间的方式较耗时,且无法记录历史表空间数据,无法判断每日表空间增长量,在没有gridcontrol/cloudcontrol软件的情况下,笔者设计如下表空间监控方案,大家也可以根据自己的实际情况对下面的方案进行修改. 二,设计思路 通过dblink将来查询到的表空间数据集中汇总到一张表里通过crontab跑定时任务从各台服务器获取表空间使用情况信息. 三,具体实施步骤   1.所在oracle数据库ip地址信息(下面为举例说明具

  • Oracle表空间数据库文件收缩案例解析

    我们经常会遇到数据库磁盘空间爆满的问题,或由于归档日志突增.或由于数据文件过多.大导致磁盘使用紧俏.这里主要说的场景是磁盘空间本身很大,但表空间对应的数据文件初始化的时候就直接顶满了磁盘空间,导致经常收到磁盘空间满的报警. 一.错误信息 告警内容如下: [发现异常]地产客储系统数据库Oracle_192.168.xx.xx,192.168.xx.xx,数据库customer,连接错误,0 ORA-00257: archiver error. Connect internal only, unti

  • Oracle表空间数据文件移动的方法

    实现把用户表空间中的数据文件从某一个路径移动到另一个路径 一.针对可offline的非系统表空间 本例移动oracle的案例表空间(EXAMPLE表空间),将其从 D:\ORADATA\ORCL\ 移动到 D:\ORACLE\ORADATA\ 1.查看要改变的表空间的数据文件信息 SQL> select tablespace_name,file_name,online_status from dba_data_files where tablespace_name='EXAMPLE'; TABL

  • Python基于codecs模块实现文件读写案例解析

    codecs在读取文件时,发生错误: UnicodeDecodeError: 'utf-8' codec can't decode byte 0xbe in position 0: invalid start byte 中文切换编码:gbk,正常解决. 但有时,utf-8和gbk都不好使用,使用notepad++,打开有乱码:看到有小块黑色. 这时候,需要加个参数, errors="ignore" # 读文件:含特殊字符 import codecs f = codecs.open('2

  • oracle表空间的创建及dmp 文件的导入(推荐)

    --用oracle系统权限的账号 登陆 -- 1.创建用户 create user u_name identified by "u_password"; --2.赋予权限 grant dba, resource, connect to u_name; grant create session to u_name; grant create table to u_name; grant create view to u_name; grant create tablespace to u

  • Javaweb使用getPart接收表单文件过程解析

    使用getPart接收表单文件时,注意Tomcat版本要在8之上. 前台 : form.jsp <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> </head> <body> <form action="${pageC

  • java读取ftp中TXT文件的案例

    最近在开发关于java读取ftp中TXT文件,其中有些坑踩了一下,再次做个记录 1.读取文件时我会根据文件名称去生成数据库表,oracle数据库对于表名的长度是有限制的,最多30个字符 2.对于多个文件的ftp的读取,每次获取文件后再次回去文件的流会为空,即在循环中多次根据ftp获取文件的流 当出现这种情况时,需要在循环时每次开启和关闭ftp的链接即可解决,否则在第二次获取的时候inputsteam为null 3.读取txt文件时,如果文件中包含中文,进行读取时可能会出现乱码,这是可设置读取的字

  • ORACLE SQL语句优化技术要点解析

    操作符优化: IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格. 但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询. 由此可见用IN的SQL至少多了一个转换的过程.一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了. 推荐方案:

  • Oracle表空间不足的两种解决办法

    首先查询表空间的大小以及文件路径地址 select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 解决方案一 //修改表空间大小(32000可改为想要的数值) alter database datafile '要修改的数据文件地址' resize 32000m; 解决方案二 //新增数据文件 ALTER

  • Oracle7.X 回滚表空间数据文件误删除处理方法

    正在看的ORACLE教程是:Oracle7.X 回滚表空间数据文件误删除处理方法. ---- 一. 引言: ---- 回滚段是数据库的一部分,它记录数据库变更的信息.使用这些信息实现数据库的读一致性及其恢复.若回滚段出现故障,则数据库不能正常启动,导致数据库瘫痪,用户无法读写数据.若能将回滚段的故障排除,则不会影响用户的实际数据.笔者最近在实际工作中遇到一个问题:用户将回滚段表空间(rbs)的数据库文件(rbs01.dbf)错误删除,以致使数据库无法正常运行,下面上笔者排除该方法的处理步骤. -

  • Oracle数据库密码文件的使用和维护

    正在看的ORACLE教程是:Oracle数据库密码文件的使用和维护.概要:Oracle关系数据库系统以其卓越的性能获得了广泛的应用,而保证数据库的安全性是数据库管理工作的重要内容.本文是笔者在总结Oracle数据库安全管理工作的基础上,对Oracle数据库系统密码文件的创建.使用和维护作了详细的介绍,供大家参考. 关键词:Oracle数据库 密码文件 在Oracle数据库系统中,用户如果要以特权用户身份(INTERNAL/SYSDBA/SYSOPER)登录Oracle数据库可以有两种身份验证的方

随机推荐