場景:操作系統相關文件系統占用馬上100%,查看發現文件都是數據庫的數據文件,想著把對應的表空間進行收縮下,不然100%會出現問題。

1.識別占用大量文件系統空間的表空間
-- 需關注 size_mb 大但是 pct_used 低的表空間。這些表空間實際使用率不高,是收縮的目標。
--free_mb 顯示的是數據文件內當前標記為“空閑”的空間,但這部分空間通常 不是 能通過 RESIZE 釋放給操作系統的空間。真正能釋放的是 HWM 之上的空間(需要通過重組表來回收)。
SELECT
tablespace_name,
file_name,
ROUND(bytes / 1024 / 1024, 2) AS size_mb,
ROUND((bytes - NVL(free_space, 0)) / 1024 / 1024, 2) AS used_mb,
ROUND(NVL(free_space, 0) / 1024 / 1024, 2) AS free_mb,
ROUND((bytes - NVL(free_space, 0)) / bytes * 100, 2) AS pct_used
FROM
(SELECT file_id, file_name, tablespace_name, bytes FROM dba_data_files)
JOIN
(SELECT file_id, SUM(bytes) AS free_space FROM dba_free_space GROUP BY file_id)
USING (file_id)
ORDER BY size_mb DESC;
查看user_simis表空間下所有的段大小合計為601g,和userd_mb幾乎一樣。
select sum(bytes/1024/1024/1024) TSize_GB from dba_segments where tablespace_name='USER_SIMIS' ;

2.解讀dba_free_space
free_mb:已經分配給oracle數據文件,尚未被數據庫對象(表、索引)占用的空間。這些空間可以直接用來存儲新數據或擴展現有對象,不需要向操作系統申請新的文件系統空間。現在還有89751M沒有使用
文件系統空間:如創建100g數據文件,操作系統會立即在文件系統上分配這100g
數據文件內部:初始可能是只有一小部分數據庫的段占用,比如10g。剩余90g是空閑的記錄在free_mb
3.是否可以收縮表空間
resize只能釋放數據文件物理末尾、連續的、未被使用的空間,無法釋放文件中間或開頭的空閑空間。
比如大部分空閑遍布在不同位置,高水位線后連續未被使用的只有很小的1g
--查找指定數據文件末尾的最大連續空閑空間 (單位 MB):
SELECT
ddf.file_id,
file_name,
ROUND((blocks * block_size) / (1024 * 1024), 2) AS current_size_mb,
ROUND((NVL(hwm_blocks, 1) * block_size) / (1024 * 1024), 2) AS hwm_position_mb,
ROUND((blocks - NVL(hwm_blocks, 1)) * block_size / (1024 * 1024), 2) AS resizable_space_mb -- 這就是能RESIZE釋放的最大空間
FROM
dba_data_files ddf,
(SELECT
file_id, MAX(block_id + blocks) AS hwm_blocks
FROM
dba_extents
GROUP BY
file_id) de,
(SELECT value AS block_size FROM v$parameter WHERE name = 'db_block_size') p
WHERE
ddf.file_id = de.file_id(+)
ORDER BY
file_id;可以發現USERSIMIS最多能縮小1.3M(文件末尾連續空閑空間),太小了,無法resize
resize可釋放的空間=hwm之后的空間,即使hwm之前大量空閑塊如free_mb不能釋放
注意:resize不會移動hwm,釋放hwm需要重組對象

4.查看碎片化較高的表
此處我們選擇表:SIMIS.AC60有數據行64445542
注意:move需要2倍的空間,如果空間不夠會報錯。
--查找高碎片化表(按碎片空間排序)
SELECT owner, table_name,
ROUND((blocks * block_size) / 1024 / 1024, 2) AS allocated_mb,
ROUND((num_rows * avg_row_len) / 1024 / 1024, 2) AS actual_data_mb,
ROUND((blocks * block_size - num_rows * avg_row_len) / 1024 / 1024, 2) AS fragment_mb,
(blocks * block_size - num_rows * avg_row_len) / (blocks * block_size) * 100 AS fragment_pct
FROM dba_tables
JOIN dba_tablespaces ON (dba_tables.tablespace_name = dba_tablespaces.tablespace_name)
WHERE dba_tables.tablespace_name = 'USER_SIMIS'
AND blocks > 0
ORDER BY fragment_mb DESC;
5.查看表區塊信息
顯示用了990304個blocks,0個空閑blocks,這時候的990304 BLOCKS即是高水位線。
select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT,INITIAL_EXTENT/1024/1024 init from DBA_segments where owner='SIMIS' and SEGMENT_NAME='AC60';

select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables a where a.TABLE_NAME='AC60' and a.OWNER='SIMIS';

6.進行move操作
alter table SIMIS.AC60 move tablespace USER_SIMIS;
查詢move進度
SELECT sid, serial#, opname, sofar, totalwork, units ,start_time,message
FROM v$session_longops
WHERE opname LIKE '%Scan%' order by start_time desc;


7.重建失效索引
select owner,index_name,status from dba_indexes a where a.table_name='AC60' AND A.owner='SIMIS';

alter index SIMIS.IDX_AAC221_AAC224 rebuild online;


8.收集表統計信息(需要重建失效索引后才能進行收集否則收集會報錯)
analyze table SIMIS.AC60 compute statistics; #用時33分鐘
9.再次查詢高水位線
select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT,INITIAL_EXTENT/1024/1024 init from DBA_segments where owner='SIMIS' and SEGMENT_NAME='AC60';

select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables a where a.TABLE_NAME='AC60' and a.OWNER='SIMIS';

10.查詢釋放數據量
1283.77-1125.65=158.12M釋放了158M,釋放的空間可能不連續,后續resize的時候可以先嘗試收縮看看,但是不能保證成功,因為釋放的空間很少,且有可能都在HWM以下。

11.收縮表空間
由于我收縮的表釋放的空間不多,且都是hwm線以下的空間,收縮會提示低于最小容許大小的錯誤。
如果HWM或任何數據塊在指定大小701479之后,resize會失敗,數據文件可能還有數據或hwm高于這個701479M點,所以不能收縮。resize必須大于或等于HWM。
大家后續嘗試的時候可以找碎片化程度高的表,多收縮幾個表,然后resize應該是沒問題的!!
ALTER DATABASE DATAFILE '/wbdata5fs/oradata/USER_SIMIS_01' RESIZE 701479M;





