段空间收缩segment shrink,从10g开始支持。
频繁的DML操作,会导致段空间产生大量空隙。
观察段空间状况:
结合表的行数,判断空间使用率。
FRAG越低,表示填满的数据块在整个段空间的比例越小,碎片越多。
压缩表:
能以在线方式使高水位线下降,无需停机,无需额外的空间。
频繁的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.btyes) 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
压缩表:
点击(此处)折叠或打开
- alter table t_table enable row movement;
- alter table t_table shrink space cascade; //下调高水线
- alter table t_table shrink space compact; //不下调高水线
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-1309462/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22621861/viewspace-1309462/