這篇文章主要介紹了Oracle表空間數(shù)據(jù)庫(kù)文件收縮的示例分析，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
我們經(jīng)常會(huì)遇到數(shù)據(jù)庫(kù)磁盤空間爆滿的問(wèn)題，或由于歸檔日志突增、或由于數(shù)據(jù)文件過(guò)多、大導(dǎo)致磁盤使用緊俏。這里主要說(shuō)的場(chǎng)景是磁盤空間本身很大，但表空間對(duì)應(yīng)的數(shù)據(jù)文件初始化的時(shí)候就直接頂滿了磁盤空間，導(dǎo)致經(jīng)常收到磁盤空間滿的報(bào)警。
一、錯(cuò)誤信息
告警內(nèi)容如下：
【發(fā)現(xiàn)異?！康禺a(chǎn)客儲(chǔ)系統(tǒng)數(shù)據(jù)庫(kù)Oracle_192.168.xx.xx，192.168.xx.xx,數(shù)據(jù)庫(kù)customer,連接錯(cuò)誤,0 ORA-00257: archiver error. Connect internal only, until freed.
【發(fā)生時(shí)間】2018.07.04 09:12:21
二、錯(cuò)誤原因
上述錯(cuò)誤一看大致就知道是由于磁盤空間不足，導(dǎo)致歸檔無(wú)法完成所致，我們只需要清理足夠的磁盤空間即可。但在磁盤清理的時(shí)候發(fā)現(xiàn)磁盤空間本身可清理的不多，被很多很大的數(shù)據(jù)文件占用，而實(shí)際使用的segment大小總共不足400G，磁盤空間本身1T，所以我們可以通過(guò)收縮數(shù)據(jù)文件的方式回收磁盤空間。
數(shù)據(jù)文件初始化方式：
1.我們創(chuàng)建表空間一般有兩種方式初始化其數(shù)據(jù)文件，即指定初始大小為32G（很大的值）或指定初始大小為100M（很小的值）然后通過(guò)自動(dòng)擴(kuò)展方式慢慢按需增長(zhǎng)。
2.第一種初始數(shù)據(jù)文件方法壞處就是開始不管你用不用到那么大，都會(huì)占用這么大的磁盤空間（這種數(shù)據(jù)遷移的時(shí)候可以使用）。第二種初始化方法按需增長(zhǎng)，比較好的監(jiān)控實(shí)際使用磁盤空間，所以推薦初始值很小，使用自動(dòng)擴(kuò)展慢慢增長(zhǎng)的方式。
三、處理步驟
1.查看磁盤空間大小
2.查看數(shù)據(jù)庫(kù)表空間大小
#!/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.查詢可直接收縮表空間數(shù)據(jù)文件
這里查看的是可以直接收縮的數(shù)據(jù)文件大小，比如最開始初始化的數(shù)據(jù)文件為32G，在數(shù)據(jù)文件高水位以下的為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.直接收縮數(shù)據(jù)文件
alter database datafile '/oracle/oradata/bi/data01.dbf' resize 1548M;
5.再次查看磁盤空間，已釋放很多，可手動(dòng)完成歸檔測(cè)試。
四、總結(jié)
針對(duì)oracle的數(shù)據(jù)文件收縮（磁盤空間收縮），我們一般可通過(guò)當(dāng)前磁盤空間查看（df -h）——>執(zhí)行可直接收縮的查詢命令和收縮命令——>執(zhí)行大表高水位收縮——>執(zhí)行表空間高水位收縮（降低文件高水位線）——>再次執(zhí)行直接回收表空間數(shù)據(jù)文件命令
直接收縮數(shù)據(jù)文件的方式參考本文上述步驟即可完成。
那么如何降低表空間的數(shù)據(jù)文件高水位，進(jìn)而完成表空間數(shù)據(jù)文件回收呢？
1.查看大于10G的數(shù)據(jù)文件
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的數(shù)據(jù)文件對(duì)應(yīng)的數(shù)據(jù)塊信息
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.查看大表對(duì)應(yīng)的數(shù)據(jù)塊信息
##查看大表 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; ##查看大表對(duì)應(yīng)的塊 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.查看數(shù)據(jù)文件對(duì)應(yīng)的大的block_id
SELECT MAX(block_id) FROM dba_extents WHERE tablespace_name = 'TABLESPACE_NAME';
6.執(zhí)行數(shù)據(jù)文件收縮
(block_id+blocks-1)數(shù)據(jù)文件的HWM alter database datafile '/oracle/oradata/bi/data01.dbf' resize xxxM;
感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“Oracle表空間數(shù)據(jù)庫(kù)文件收縮的示例分析”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!
重慶分公司
重慶分公司
