段空间收缩segment shrink，从10g开始支持。
频繁的DML操作，会导致段空间产生大量空隙。
观察段空间状况：
点击( 此处)折叠或打开
-
select
sum
(bytes
)
/1024
/1024
-
from dba_segments
- where segment_name = 't_table' ;
结合表的行数，判断空间使用率。
点击( 此处)折叠或打开
- col frag format 999999 .99
-
col owner format a30
col table_name format a30
select * from (
select a.owner,
a.table_name,
a.num_rows,
a.avg_row_len * a.num_rows,
sum(b.bytes),
(a.avg_row_len * a.num_rows) / sum(b.bytes) frag
from dba_tables a, dba_segments 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')
group by a.owner, a.table_name, a.avg_row_len, a.num_rows
having a.avg_row_len * a.num_rows / sum(b.bytes) < 0.7
order by sum(b.bytes) desc)
where rownum <= 100;
-
OWNER TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
A.AVG_ROW_LEN*A.NUM_ROWS SUM(B.BYTES) FRAG
------------------------ ------------ ----------
SH PRODUCTS 72
12744 1048576 .01
SH PROMOTIONS 503
49294 1048576 .05
SH T1 128
1280 1048576 .00
FRAG越低，表示填满的数据块在整个段空间的比例越小，碎片越多。
压缩表：
点击( 此处)折叠或打开
-
alter
table t_table enable
row movement
;
- alter table t_table shrink space cascade ; //下调高水线
-
alter
table
t_table shrink space compact
; //不下调高水线
能以在线方式使高水位线下降，无需停机，无需额外的空间。