oracle 碎片高的表,Oracle 表格碎片的查看方法

参考：How to Find Fragmentation for Tables and LOBs (文档 ID 2132004.1)

1. Tables in MSSM (Manual Segment Space Management) tablespaces (对于手动段管理方式表空间下的表)

exec dbms_stats.gather_table_stats(‘‘,‘

select owner,table_name,round((blocks*8),2)||‘ kb‘ "TABLE SIZE",round((num_rows*avg_row_len/1024),2)||‘ kb‘ "ACTUAL DATA" from dba_tables where table_name=‘‘;

2. Tables in ASSM(Automatic Segment Space Management) tablespaces (对于自动段管理方式表空间下的表)

set serveroutput on

declare

v_unformatted_blocks number;

v_unformatted_bytes number;

v_fs1_blocks number;

v_fs1_bytes number;

v_fs2_blocks number;

v_fs2_bytes number;

v_fs3_blocks number;

v_fs3_bytes number;

v_fs4_blocks number;

v_fs4_bytes number;

v_full_blocks number;

v_full_bytes number;

begin

dbms_space.space_usage (‘‘, ‘

v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

dbms_output.put_line(‘Unformatted Blocks = ‘||v_unformatted_blocks);

dbms_output.put_line(‘FS1 Blocks = ‘||v_fs1_blocks);

dbms_output.put_line(‘FS2 Blocks = ‘||v_fs2_blocks);

dbms_output.put_line(‘FS3 Blocks = ‘||v_fs3_blocks);

dbms_output.put_line(‘FS4 Blocks = ‘||v_fs4_blocks);

dbms_output.put_line(‘Full Blocks = ‘||v_full_blocks);

end;

/

unformatted_blocks : Total number of blocks unformatted. unformatted blocks are blocks that are available for immediate use (below the true high water mark) but haven‘t yet had any data. when the table says I‘m full, we pull a bunch of blocks down into the table from above the HWM and they would all be unformatted until you use them.

fs1_blocks : Number of blocks having at least 0 to 25% free space

fs2_blocks : Number of blocks having at least 25 to 50% free space

fs3_blocks : Number of blocks having at least 50 to 75% free space

fs4_blocks : Number of blocks having at least 75 to 100% free space

ful1_blocks : Total number of blocks full in the segment

3.To find fragmentation at partition level (分区基本上查看碎片)

set serveroutput on

declare

v_unformatted_blocks number;

v_unformatted_bytes number;

v_fs1_blocks number;

v_fs1_bytes number;

v_fs2_blocks number;

v_fs2_bytes number;

v_fs3_blocks number;

v_fs3_bytes number;

v_fs4_blocks number;

v_fs4_bytes number;

v_full_blocks number;

v_full_bytes number;

begin

dbms_space.space_usage (‘‘, ‘

v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, );

dbms_output.put_line(‘Unformatted Blocks = ‘||v_unformatted_blocks);

dbms_output.put_line(‘FS1 Blocks = ‘||v_fs1_blocks);

dbms_output.put_line(‘FS2 Blocks = ‘||v_fs2_blocks);

dbms_output.put_line(‘FS3 Blocks = ‘||v_fs3_blocks);

dbms_output.put_line(‘FS4 Blocks = ‘||v_fs4_blocks);

dbms_output.put_line(‘Full Blocks = ‘||v_full_blocks);

end;

/

原文：https://www.cnblogs.com/sky2088/p/13563253.html