转载 查表碎片及整理碎片

--查表碎片管理员用户下

select owner, segment_name table_name, segment_type type,
greatest(round(100 * (nvl(hwm - avg_used_blocks,0)/greatest(nvl(hwm,1),1) ), 2), 0) "waste_per(%)",
round(bytes/1024/1024, 2) "total(m)", num_rows,
blocks, empty_blocks, hwm highwater_mark, avg_used_blocks,chain_per, extents, max_extents, allo_extent_per,
decode(greatest(max_free_space - next_extent, 0), 0,'n','y') can_extend_space,
next_extent, max_free_space,
o_tablespace_name tablespace_name
from
(select a.owner owner, a.segment_name, a.segment_type, a.bytes,
b.num_rows, a.blocks blocks, b.empty_blocks empty_blocks,
a.blocks - b.empty_blocks - 1 hwm,
decode( round((b.avg_row_len * num_rows * (1 + (pct_free/100)))/c.blocksize, 0),0, 1,
round((b.avg_row_len * num_rows * (1 + (pct_free/100)))/c.blocksize, 0)) + 2 avg_used_blocks,
round(100 * (nvl(b.chain_cnt, 0)/greatest(nvl(b.num_rows, 1), 1)), 2) chain_per,
round(100 * (a.extents/a.max_extents), 2) allo_extent_per,a.extents extents,
a.max_extents max_extents, b.next_extent next_extent, b.tablespace_name o_tablespace_name
from sys.dba_segments a,sys.dba_tables b,sys.ts$ c
where a.owner =b.owner
and segment_name = table_name
and segment_type = 'table'
and b.tablespace_name = c.name
union all
select a.owner owner, segment_name || '.' || b.partition_name, segment_type, bytes,
b.num_rows, a.blocks blocks, b.empty_blocks empty_blocks,
a.blocks - b.empty_blocks - 1 hwm,
decode( round((b.avg_row_len * b.num_rows * (1 + (b.pct_free/100)))/c.blocksize, 0),0, 1,
round((b.avg_row_len * b.num_rows * (1 + (b.pct_free/100)))/c.blocksize, 0)
) + 2 avg_used_blocks,
round(100 * (nvl(b.chain_cnt,0)/greatest(nvl(b.num_rows, 1), 1)), 2) chain_per,
round(100 * (a.extents/a.max_extents), 2) allo_extent_per, a.extents extents,
a.max_extents max_extents, b.next_extent,
b.tablespace_name o_tablespace_name
from sys.dba_segments a,sys.dba_tab_partitions b,sys.ts$ c,sys.dba_tables d
where a.owner = b.table_owner
and segment_name = b.table_name
and segment_type = 'table partition'
and b.tablespace_name = c.name
and d.owner = b.table_owner
and d.table_name = b.table_name
and a.partition_name = b.partition_name),
(select tablespace_name f_tablespace_name,max(bytes) max_free_space
from sys.dba_free_space
group by tablespace_name)
where f_tablespace_name = o_tablespace_name and
greatest(round(100 * (nvl(hwm - avg_used_blocks, 0)/greatest(nvl(hwm, 1), 1) ), 2), 0) > 25
and blocks > 128
and owner in ('scott')
order by 1 asc, 4 desc;

 

select * from ha_wxzj.tpr_zjyw1
alter table ha_wxzj.tpr_zjyw1 enable row movement;
--整理碎片
alter table ha_wxzj.tpr_zjyw1 shrink space;

-----------------说明如下，要认真看完--------------------------------------------------------------------------------------------------------
如果经常在表上执行DML操作，会造成数据库块中数据分布稀疏，浪费大量空间。同时也会影响全表扫描的性能，因为全表扫描需要访问更多的数据块。
从oracle10g开始，表可以通过shrink来重组数据使数据分布更紧密，同时降低HWM释放空闲数据块。

segment shrink分为两个阶段：

1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，
需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。

2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

shrink space语句两个阶段都执行。
shrink space compact只执行第一个阶段。
如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。
注意：alter table XXX enable row movement语句会造成引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。