Oracle表空間數(shù)據(jù)庫文件收縮案例解析
我們經(jīng)常會(huì)遇到數(shù)據(jù)庫磁盤空間爆滿的問題，或由于歸檔日志突增、或由于數(shù)據(jù)文件過多、大導(dǎo)致磁盤使用緊俏。這里主要說的場景是磁盤空間本身很大，但表空間對(duì)應(yīng)的數(shù)據(jù)文件初始化的時(shí)候就直接頂滿了磁盤空間，導(dǎo)致經(jīng)常收到磁盤空間滿的報(bào)警。
一、錯(cuò)誤信息
告警內(nèi)容如下：
【發(fā)現(xiàn)異常】地產(chǎn)客儲(chǔ)系統(tǒng)數(shù)據(jù)庫Oracle_192.168.xx.xx，192.168.xx.xx,數(shù)據(jù)庫customer,連接錯(cuò)誤,0 ORA-00257: archiver error. Connect internal only, until freed.
【發(fā)生時(shí)間】2018.07.04 09:12:21
二、錯(cuò)誤原因
上述錯(cuò)誤一看大致就知道是由于磁盤空間不足，導(dǎo)致歸檔無法完成所致，我們只需要清理足夠的磁盤空間即可。但在磁盤清理的時(shí)候發(fā)現(xiàn)磁盤空間本身可清理的不多，被很多很大的數(shù)據(jù)文件占用，而實(shí)際使用的segment大小總共不足400G，磁盤空間本身1T，所以我們可以通過收縮數(shù)據(jù)文件的方式回收磁盤空間。
數(shù)據(jù)文件初始化方式：
1.我們創(chuàng)建表空間一般有兩種方式初始化其數(shù)據(jù)文件，即指定初始大小為32G（很大的值）或指定初始大小為100M（很小的值）然后通過自動(dòng)擴(kuò)展方式慢慢按需增長。
2.第一種初始數(shù)據(jù)文件方法壞處就是開始不管你用不用到那么大，都會(huì)占用這么大的磁盤空間（這種數(shù)據(jù)遷移的時(shí)候可以使用）。第二種初始化方法按需增長，比較好的監(jiān)控實(shí)際使用磁盤空間，所以推薦初始值很小，使用自動(dòng)擴(kuò)展慢慢增長的方式。
三、處理步驟
1.查看磁盤空間大小
2.查看數(shù)據(jù)庫表空間大小
#!/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.查詢可直接收縮表空間數(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)完成歸檔測試。
四、總結(jié)
針對(duì)oracle的數(shù)據(jù)文件收縮（磁盤空間收縮），我們一般可通過當(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;
總結(jié)
以上所述是小編給大家介紹的Oracle表空間數(shù)據(jù)庫文件收縮案例解析，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
相關(guān)文章
VMware中l(wèi)inux環(huán)境下oracle安裝圖文教程（一）
剛剛接觸ORACLE的人來說,從那里學(xué),如何學(xué),有那些工具可以使用,應(yīng)該執(zhí)行什么操作,一定回感到無助。所以在學(xué)習(xí)使用ORACLE之前，首先來安裝一下ORACLE 10g，在來掌握其基本工具。俗話說的好：工欲善其事，必先利其器。作為一個(gè)新手，我們還是先在VMware虛擬機(jī)里安裝吧。2014-08-08
windows中oracle存儲(chǔ)過程加密的實(shí)例代碼
這篇文章主要介紹了windows中oracle存儲(chǔ)過程加密的實(shí)現(xiàn)方法，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-01-01
深入Oracle的left join中on和where的區(qū)別詳解
本篇文章是對(duì)Oracle的left join中on和where的區(qū)別進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
在oracle數(shù)據(jù)庫中sql使用in時(shí),如果in的能數(shù)超過1000就會(huì)出問題,下面這篇文章主要給大家介紹了關(guān)于sql?in查詢?cè)爻^1000條的解決方案,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03
PL/SQL Developer連接64位的Oracle圖文教程
這篇文章主要為大家詳細(xì)介紹了PL/SQL Developer連接64位的Oracle圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-03-03
近期安裝完oracle 10g后，竟發(fā)現(xiàn)將安裝時(shí)設(shè)置的系統(tǒng)密碼忘記。在同事的幫助下通過一下方法解決,遇到同樣問題的朋友可以參考2012-11-11
Oracle靜態(tài)注冊(cè)與動(dòng)態(tài)注冊(cè)詳解
這篇文章主要介紹了Oracle靜態(tài)注冊(cè)與動(dòng)態(tài)注冊(cè),需要的朋友可以參考下2014-07-07
這篇文章主要介紹了ORACLE批量導(dǎo)入圖片到BLOB字段代碼示例，此代碼示例是文章作者的項(xiàng)目源碼，具有一定參考價(jià)值，需要的朋友可以了解下。2017-10-10
ORACLE中查找定位表最后DML操作的時(shí)間小結(jié)
在Oracle數(shù)據(jù)庫中，如何查找，定位一張表最后一次的DML操作的時(shí)間呢？ 方式有三種，不過都有一些局限性，下面簡單的解析、總結(jié)一下。感興趣的朋友跟隨小編一起看看吧2018-11-11
如何Shrink Undo表空間,釋放過度占用的空間...2007-03-03