这篇文章主要介绍了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/bashsqlplus-S/nolog<<EOFconn/assysdba;setechooffheadingonunderlineon;columninst_numheading"InstNum"new_valueinst_numformat99999;columninst_nameheading"Instance"new_valueinst_nameformata12;columndb_nameheading"DBName"new_valuedb_nameformata12;columndbidheading"DBId"new_valuedbidformat9999999999justc;promptpromptCurrentInstanceprompt~~~~~~~~~~~~~~~~selectd.dbiddbid,d.namedb_name,i.instance_numberinst_num,i.instance_nameinst_namefromv\$databased,v\$instancei;settermonfeedbackofflines130pagesize999tabofftrimsoncolumnMBformat999,999,999heading"TotalMB"columnfreeformat9,999,999heading"FreeMB"columnusedformat99,999,999heading"UsedMB"columnLargestformat999,999heading"LrgstMB"columntablespace_nameformata20heading"Tablespace"columnstatusformata3truncatedcolumnmax_extentsformat99999999999heading"MaxExt"colextent_managementfora1trunchead"M"colallocation_typefora1trunchead"A"colExt_Sizefora4trunchead"Init"columnpfreeformata3truncheading"%Fr"breakonreportcomputesumofMBonreportcomputesumoffreeonreportcomputesumofusedonreportselectd.tablespace_name,decode(d.status,'ONLINE','OLN','READONLY','R/O',d.status)status,d.extent_management,decode(d.allocation_type,'USER','',d.allocation_type)allocation_type,(casewheninitial_extent<1048576thenlpad(round(initial_extent/1024,0),3)||'K'elselpad(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,(casewhenround(f.bytes/a.bytes*100,0)>=20then''else'*'end)alrtFROMsys.dba_tablespacesd,(SELECTtablespace_name,SUM(bytes)bytesFROMdba_data_filesGROUPBYtablespace_name)a,(SELECTtablespace_name,SUM(bytes)bytesFROMdba_free_spaceGROUPBYtablespace_name)f,(SELECTtablespace_name,MAX(bytes)largeFROMdba_free_spaceGROUPBYtablespace_name)lWHEREd.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=f.tablespace_name(+)ANDd.tablespace_name=l.tablespace_name(+)ANDNOT(d.extent_managementLIKE'LOCAL'ANDd.contentsLIKE'TEMPORARY')UNIONALLselectd.tablespace_name,decode(d.status,'ONLINE','OLN','READONLY','R/O',d.status)status,d.extent_management,decode(d.allocation_type,'UNIFORM','U','SYSTEM','A','USER','',d.allocation_type)allocation_type,(casewheninitial_extent<1048576thenlpad(round(initial_extent/1024,0),3)||'K'elselpad(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,(casewhennvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100)>=20then''else'*'end)alrtFROMsys.dba_tablespacesd,(SELECTtablespace_name,SUM(bytes)bytesFROMdba_temp_filesGROUPBYtablespace_nameorderbytablespace_name)a,(SELECTtablespace_name,SUM(bytes_used)bytesFROMv\$temp_extent_poolGROUPBYtablespace_name)t,(SELECTtablespace_name,MAX(bytes_cached)largeFROMv\$temp_extent_poolGROUPBYtablespace_nameorderbytablespace_name)lWHEREd.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=t.tablespace_name(+)ANDd.tablespace_name=l.tablespace_name(+)ANDd.extent_managementLIKE'LOCAL'ANDd.contentsLIKE'TEMPORARY'ORDERby1/promptexitEOF

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

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

selecta.file#,a.name,a.bytes/1024/1024CurrentMB,ceil(HWM*a.block_size)/1024/1024ResizeTo,(a.bytes-HWM*a.block_size)/1024/1024ReleaseMB,'alterdatabasedatafile'''||a.name||'''resize'||ceil(HWM*a.block_size/1024/1024)||'M;'ResizeCMDfromv$datafilea,(selectfile_id,max(block_id+blocks-1)HWMfromdba_extentsgroupbyfile_id)bwherea.file#=b.file_id(+)and(a.bytes-HWM*block_size)>0;

4.直接收缩数据文件

alterdatabasedatafile'/oracle/oradata/bi/data01.dbf'resize1548M;

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

四、总结

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

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

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

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

selectfile_name,file_id,tablespace_name,(bytes/1024/1024/1024)file_size_gbfromdba_data_fileswhere(bytes/1024/1024/1024)>10orderbyfile_id;

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

selectfile_id,max(block_id+blocks-1)HWM,block_idfromdba_extentswherefile_id=14groupbyfile_id,block_idorderbyhwmdesc;

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

##查看大表selectfile_name,file_id,tablespace_name,(bytes/1024/1024/1024)file_size_gbfromdba_data_fileswhere(bytes/1024/1024/1024)>10orderbyfile_id;##查看大表对应的块selectowner,segment_name,file_id,block_id,blocksfromdba_extentswheresegment_name='TABLE_NAME';

4.降低表的高水位

altertabletable_namemove;alterindexidx_namerebuild;

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

SELECTMAX(block_id)FROMdba_extentsWHEREtablespace_name='TABLESPACE_NAME';

6.执行数据文件收缩

(block_id+blocks-1)数据文件的HWMalterdatabasedatafile'/oracle/oradata/bi/data01.dbf'resizexxxM;

感谢你能够认真阅读完这篇文章，希望小编分享的“Oracle表空间数据库文件收缩的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!