ASSM管理模式下可以用 alter table hwm shrink space;回收高水位
show_space脚本
create or replace procedure show_space
( p_segname_1 in varchar2,
p_space in varchar2 default 'MANUAL',
p_type_1 in varchar2 default 'TABLE' ,
p_analyzed in varchar2 default 'N',
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;
if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;
if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;
if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
p( 'Free Blocks', l_free_blks );
end if;
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);
end if;
end;
SQL> set serveroutput on
SQL> exec show_space('HWM','AUTO');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................64
Unused Bytes............................524288
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................64
PL/SQL procedure successfully completed.
SQL> delete hwm;
49788 rows deleted.
SQL> commit;
Commit complete.
SQL> exec show_space('HWM','AUTO');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................64
Unused Bytes............................524288
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................64
PL/SQL procedure successfully completed.
SQL>
SQL> alter table hwm enable row movement;
Table altered.
SQL> alter table hwm shrink space;
Table altered.
SQL> exec show_space('HWM','AUTO');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................4
Last Used Ext BlockId...................81
Last Used Block.........................4
PL/SQL procedure successfully completed.
SQL>
请教 表碎片问题：
系统24*7的这张表进行insert 和delete .造成了大量的碎片
如果用exp和imp，因为系统是24*7的。所以此方法行不通
对表进行move ，因为不停的有进程对这一张表进行delete 和insert，每次move的时侯提示“资源正忙”，move也无法成功!
请教还有什么办法解决这种问题?难道只能用10G的在线回收高水位。
还有一个问题 是如果查出碎片程序高的表，
我现在用的查高碎片程度的sql是需要将表进行分析，有没有不要将表进行分析即可以知道碎片程度高的sql语句
eg:有我一个表(os_software)碎片程序高达99%.天哪 表接近2个G，可是我那天create table os_software_new as select * from os_software;
os_software_new只占用2M的空间。。太碎片了，，，
请高人帮忙看一下，我的这个表是由于空闲块多，还是每个块上只有一点点数据，造成了表破碎。。。
(1)SQL> SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME='OS_SOFTWARE_OLD';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_POOL
------------------ ------------------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -----------
OS_SOFTWARE_OLD TABLE PERF 1960837120 239360 935 2097152 2097152 1 2147483645 0 DEFAULT
-->这个表占用快2G的空间
(2)SQL> SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='PERF';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------
PERF 8192 2097152 2097152 1 2147483645 0 2097152 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO
-->perf表空间是本地管理，unform. size 2M 扩展,segment空间管理是采用 oracle自动管理
(3)sql> ANALYZE TABLE OS_SOFTWARE_OLD COMPUTE STATISTICS;
--对表进行分析后：
(3)sql> ANALYZE TABLE OS_SOFTWARE_OLD COMPUTE STATISTICS;
--对表进行分析后：
SQL> select t.table_name,t.pct_free,t.pct_used,
2 t.initial_extent,t.next_extent,t.num_rows,t.blocks,t.empty_blocks,t.avg_space,t.avg_row_len,t.chain_cnt
3 from USER_TABLES t where table_name='OS_SOFTWARE_OLD';
TABLE_NAME PCT_FREE PCT_USED INITIAL_EXTENT NEXT_EXTENT NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
-------------------------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- ----------- ----------
OS_SOFTWARE_OLD 10 2097152 2097152 73571 238656 704 7767 66 0
-->可以看出表NUM_ROWS =73571，，BLOCKS=238656， EMPTY_BLOCKS=704 ，CHAIN_CNT =0
AVG_ROW_LEN*NUM_ROWS=4.7M 说明总数据只有5M？
是不是可以说明表没有太多的空闲块，没有行链接，很可能是每个块上只有一点点数据？？
再根据网络上的sql查询碎片程度高的表
OWNER TABLE_NAME SEGMENT_TYPE WASTE_PER TABLE_KB NUM_ROWS BLOCKS EMPTY_BLOCKS HIGHWATER_MARK AVG_USED_BLOCKS CHAIN_PER EXTENTS MAX_EXTENTS ALLO_EXTENT_PER CAN_EXTEND_SPACE NEXT_EXTENT MAX_FREE_SPACE TABLESPACE_NAME
--------- --------------------- ------------------ ---------- ---------- ---------- ---------- ------------ -------------- --------------- ---------- ---------- ----------- --------------- -------------------------------- ----------- -------------- ------------------------------
ITNMS OS_SOFTWARE_OLD TABLE 99.73 1914880 73571 239360 704 238655 654 0 935 2147483645 0 Y 2097152 608174080 PERF
--说了这么多，，我还没有没有办法来对付这个高破碎的表
在设计层面上优化，还是有其它方法，盼请高人指点一二!!!!
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7301064/viewspace-442900/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7301064/viewspace-442900/
扫一扫
161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
