快捷導(dǎo)航

oracle表碎片查詢整理過程

更新時(shí)間：2026年01月06日 14:32:12 作者：MrZhou周

本文通過模擬插入、清理、刪除數(shù)據(jù),以及表碎片整理,詳細(xì)記錄了表的碎片率和浪費(fèi)空間的變化,最終通過表碎片整理釋放了浪費(fèi)的空間并降低了高水位,為數(shù)據(jù)庫管理提供了參考

oracle表碎片查詢整理

模擬插入數(shù)據(jù)

create table test as select * from dba_objects;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
commit;

收集統(tǒng)計(jì)信息

SET TIME ON
exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

查詢初始表的碎片率68126

SELECT table_name,
ROUND ( (blocks * 8), 2) "HW_space k",
ROUND ( (num_rows * avg_row_len / 1024), 2) "real_space k",
ROUND ( (blocks * 10 / 100) * 8, 2) "(pctfree) k",
ROUND (
( blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100),
2)
"waste_space k"
FROM user_tables
WHERE temporary = 'N' AND TABLE_NAME='TEST'
--and OWNER='TEST'
ORDER BY 5 DESC;

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST            89176     68126.08      8917.6      12132.32

第一次模擬清理數(shù)據(jù)

delete from test where object_type in('SYNONYM','JAVA CLASS');
commit;

再次收集統(tǒng)計(jì)信息

SET TIME ON
exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

第一次清理完查詢表的碎片率18963

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST            89176     18963.03      8917.6      61295.37

第二次模擬刪除數(shù)據(jù)

delete from test where object_type in('VIEW','INDEX','TABLE','TYPE');
commit;

再次收集統(tǒng)計(jì)信息

SET TIME ON
exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

第二次清理完查詢表的碎片率6199

但是可以看到浪費(fèi)的空間一直在增加74058.9，高水位一直沒有變化89176

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST            89176       6199.5      8917.6       74058.9

進(jìn)行表碎片整理

15:21:46 SQL> alter table test enable row movement;

Table altered.

15:24:52 SQL> alter table test shrink space;

Table altered.

15:25:23 SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=> 'TEST',DEGREE=>2);

PL/SQL procedure successfully completed.

15:25:47 SQL> SELECT table_name,
15:25:56   2  ROUND ( (blocks * 8), 2) "HW_space k",
15:25:56   3  ROUND ( (num_rows * avg_row_len / 1024), 2) "real_space k",
15:25:56   4  ROUND ( (blocks * 10 / 100) * 8, 2) "(pctfree) k",
15:25:56   5  ROUND (
15:25:56   6  ( blocks * 8
15:25:56   7  - (num_rows * avg_row_len / 1024)
15:25:56   8  - blocks * 8 * 10 / 100),
2)
15:25:56   9  15:25:56  10  "waste_space k"
FROM user_tables
15:25:56  11  15:25:56  12  WHERE temporary = 'N' AND TABLE_NAME='TEST'
15:25:56  13  --and OWNER='TEST'
15:25:56  14  ORDER BY 5 DESC;

TABLE_NAME HW_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
TEST             7408       6199.5       740.8         467.7

經(jīng)過以上表碎片整理之后，可以看到表的高水位已經(jīng)降下來7408，浪費(fèi)的空間已經(jīng)得到釋放467.7