很多時候，對于某張表有大量的 delete 刪除操作，但是發現空間并未釋放，這是高水位未下降的原因，何為“高水位線”大概就是定義為 Oracle 段中已使用和未使用空間之間的分界。大量刪除操作會導致表的碎片過多，從而影響性能，檢查表的碎片率一般使用存儲過程 SPACE_USAGE 程序。下面詳細介紹一下 SPACE_USAGE 程序。
SPACE_USAGE 程序
此過程有兩個變體來顯示空間使用情況。
程序的第一種形式顯示了高水位線段下數據塊的空間使用情況。 您可以計算 LOB、LOB 分區和 LOB 子分區的使用率。 這個過程只能用于使用自動段空間管理創建的表空間。 位圖塊、段頭和區段圖塊不在這個過程中計算。 注意，此重載不能在 SECUREFILE LOB 上使用。
過程的第二種形式返回有關 SECUREFILE LOB 空間使用情況的信息。 它將返回 LOB 段中所有 SECUREFILE LOB 所使用的塊中的空間量。 這個過程顯示 LOB 列正在使用的空間、過期保留的已釋放空間和未過期保留的已釋放空間。 注意，這種重載只能在SECUREFILE LOB 上使用。
對于 LOB 段，從返回的full_blocks塊unformatted_blocks數實際上是 LOB 段的塊數。
語法 1：
DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
unformatted_blocks OUT NUMBER,
unformatted_bytes OUT NUMBER,
fs1_blocks OUT NUMBER,
fs1_bytes OUT NUMBER,
fs2_blocks OUT NUMBER,
fs2_bytes OUT NUMBER,
fs3_blocks OUT NUMBER,
fs3_bytes OUT NUMBER,
fs4_blocks OUT NUMBER,
fs4_bytes OUT NUMBER,
full_blocks OUT NUMBER,
full_bytes OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);
語法 2：
DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
segment_size_blocks OUT NUMBER,
segment_size_bytes OUT NUMBER,
used_blocks OUT NUMBER,
used_bytes OUT NUMBER,
expired_blocks OUT NUMBER,
expired_bytes OUT NUMBER,
unexpired_blocks OUT NUMBER,
unexpired_bytes OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);
各字段含義：
普通表碎片查看示例
set serverout on size 1000000
declare
p_fs1_bytes number;
p_fs2_bytes number;
p_fs3_bytes number;
p_fs4_bytes number;
p_fs1_blocks number;
p_fs2_blocks number;
p_fs3_blocks number;
p_fs4_blocks number;
p_full_bytes number;
p_full_blocks number;
p_unformatted_bytes number;
p_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => 'TEST',
segment_name => 'ORIGINAL',
segment_type => 'TABLE',
fs1_bytes => p_fs1_bytes,
fs1_blocks => p_fs1_blocks,
fs2_bytes => p_fs2_bytes,
fs2_blocks => p_fs2_blocks,
fs3_bytes => p_fs3_bytes,
fs3_blocks => p_fs3_blocks,
fs4_bytes => p_fs4_bytes,
fs4_blocks => p_fs4_blocks,
full_bytes => p_full_bytes,
full_blocks => p_full_blocks,
unformatted_blocks => p_unformatted_blocks,
unformatted_bytes => p_unformatted_bytes
);
dbms_output.put_line('FS1: blocks = '||p_fs1_blocks);
dbms_output.put_line('FS2: blocks = '||p_fs2_blocks);
dbms_output.put_line('FS3: blocks = '||p_fs3_blocks);
dbms_output.put_line('FS4: blocks = '||p_fs4_blocks);
dbms_output.put_line('Full blocks = '||p_full_blocks);
end;
/
FS1: blocks = 0
FS2: blocks = 2
FS3: blocks = 0
FS4: blocks = 0
Full blocks = 11
FS1表明有 0 個數據塊具有 0%-25% 的空閑空間，FS2 表明有 2 個數據塊具有 25%-50% 的空閑空，間FS3 表明有 0 個數據塊具有 50%-75% 的空閑空間，FS4 表明有 0 個數據塊具有 75%-100% 繁榮空閑空間，FULL 表明有 11個 滿的數據塊。
分區表碎片查看示例
--分區表碎片查看
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('TEST', 'ORIGINAL', 'TABLE PARTITION', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, 'PAR1');
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
分區表相關視圖
上面示例需要查詢分區名，一般使用 DBA_TAB_PARTITIONS 視圖如下 SQL 查看，以下收集了關于分區表的相關視圖，有需要的可以看看。
col TABLE_OWNER for a30
col TABLE_NAME for a30
col PARTITION_NAME for a30
col TABLESPACE_NAME for a30
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER='TEST';
- 顯示當前用戶可訪問的所有分區表信息﹕
???? ALL_PART_TABLES - 顯示當前用戶所有分區表的信息﹕
???? USER_PART_TABLES - 顯示表分區信息?顯示數據庫所有分區表的詳細分區信息﹕
???? DBA_TAB_PARTITIONS - 顯示當前用戶可訪問的所有分區表的詳細分區信息﹕
???? ALL_TAB_PARTITIONS - 顯示當前用戶所有分區表的詳細分區信息﹕
???? USER_TAB_PARTITIONS - 顯示子分區信息?顯示數據庫所有組合分區表的子分區信息﹕
???? DBA_TAB_SUBPARTITIONS - 顯示當前用戶可訪問的所有組合分區表的子分區信息﹕
???? ALL_TAB_SUBPARTITIONS - 顯示當前用戶所有組合分區表的子分區信息﹕
???? USER_TAB_SUBPARTITIONS - 顯示分區列?顯示數據庫所有分區表的分區列信息﹕
???? DBA_PART_KEY_COLUMNS
10.顯示當前用戶可訪問的所有分區表的分區列信息﹕
???? ALL_PART_KEY_COLUMNS
11.顯示當前用戶所有分區表的分區列信息﹕
???? USER_PART_KEY_COLUMNS
12.顯示子分區列?顯示數據庫所有分區表的子分區列信息﹕
???? DBA_SUBPART_KEY_COLUMNS
13.顯示當前用戶可訪問的所有分區表的子分區列信息﹕
???? ALL_SUBPART_KEY_COLUMNS
14.顯示當前用戶所有分區表的子分區列信息﹕
???? USER_SUBPART_KEY_COLUMNS
用戶級別查看碎片
當檢查的表比較多時，甚至是全庫時，使用如下 SQL 查看。
set lines 200 pages 1000
col frag format 999999.99
col owner format a30;
col table_name format a30;
col frag for a20
select a.owner,
a.table_name,
a.num_rows,
a.avg_row_len,
round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB,
round(b.seg_bytes_mb, 2) seg_bytes_mb,
decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) || '%' frag_percent
from dba_tables a,
(select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb
from dba_segments
group by owner, segment_name) b
where a.table_name = b.segment_name
and a.owner = b.owner
--and a.owner not in ('SYS','SYSTEM','OUTLN','DMSYS','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','OLAPSYS','ORDSYS','MDSYS','SYSMAN')
and a.owner in ('TEST_JIEKE','PROD','SCOTT')
--and a.table_name='T_ZDW_DOWN_SYNC_REC'
and decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) > 50
order by b.seg_bytes_mb desc;
表碎片整理
通過碎片整理來收縮空間，調整高水位線，方法比較簡單，首先需要啟用行移動，然后 alter table …… shrink space 即可完成，期間不會阻塞 DML 操作，可能時間會很久，建議業務低峰期間操作。
示例如下：
--啟用行移動功能
alter table prod.T_DOWN_SYNC_REC enable row movement;
--收縮表
alter table prod.T_DOWN_SYNC_REC shrink space cascade;
--cascade 可以收縮與索引段相關的空間
--禁用行移動功能
alter table prod.T_DOWN_SYNC_REC disable row movement;
除了使用 shrink space 外，還有截斷表，移動表，導入導出。但是 truncate 表直接清理數據，一般情況下不可取；alter table t move;移動表會使索引失效，移動完需要 rebuild 重新建索引，移動表時對數據行的 rowid 有所變更，而索引中又包含了 rowid, 故 move 表會使索引失效。最后導出導入數據時，不能有新數據進入，這點也不太友好，故此推薦 shrink space 。
全文完，希望可以幫到正在閱讀的你~~~
————————————————————————————
公眾號：JiekeXu DBA之路
墨天輪：http://www.sunline.cc/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
騰訊云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
