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  <= 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表空间数据库文件收缩案例解析，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对潘少俊衡网站的支持！