共計 6092 個字符，預計需要花費 16 分鐘才能閱讀完成。
這篇文章主要介紹了 Oracle 表空間數據庫文件收縮的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。
我們經常會遇到數據庫磁盤空間爆滿的問題，或由于歸檔日志突增、或由于數據文件過多、大導致磁盤使用緊俏。這里主要說的場景是磁盤空間本身很大，但表空間對應的數據文件初始化的時候就直接頂滿了磁盤空間，導致經常收到磁盤空間滿的報警。
一、錯誤信息
告警內容如下：
【發現異常】地產客儲系統數據庫 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
EOF3. 查詢可直接收縮表空間數據文件
這里查看的是可以直接收縮的數據文件大小，比如最開始初始化的數據文件為 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) || 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)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_NAME6. 執行數據文件收縮
(block_id+blocks-1) 數據文件的 HWM
alter database datafile /oracle/oradata/bi/data01.dbf resize xxxM;感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“Oracle 表空間數據庫文件收縮的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!
