检查oracle表和索引可回收空间！！

不错的文章~~工作中很常用。
记录学习。
交流群127591054
原帖地址：http://www.3lian.com/edu/2013/11-29/111321.html

对表进行大量删除后，可能会有很多空闲空间可以回收，相关计算方法参考如下：

　　更新统计信息

　　Analyze table compute statistics ;

　　计算碎片空间

　　SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)

　　"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;

　　ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

　　SELECT table_name,num_rows,blocks,empty_blocks

　　FROM user_tables

　　WHERE table_name='BIG_EMP1';

　　SELECT COUNT (DISTINCT

　　DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||

　　DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"

　　FROM big_emp1;

　　SELECT segment_name,segment_type,blocks

　　FROM dba_segments

　　WHERE segment_name='BIG_EMP1';

　　对于索引

　　校验结构

　　analyze index validate structure;

　　检查

　　column name format a15

　　column blocks heading "ALLOCATED|BLOCKS"

　　column lf_blks heading "LEAF|BLOCKS"

　　column br_blks heading "BRANCH|BLOCKS"

　　column Empty heading "UNUSED|BLOCKS"

　　select name,

　　blocks,

　　lf_blks,

　　br_blks,

　　blocks-(lf_blks+br_blks) empty

　　from index_stats;

　　或者

　　select name, btree_space, used_space, pct_used from index_stats;

　　回收空间方法

　　'Compatible' 必须 >=10.0

　　1. Enable row movement for the table.

　　SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

　　2. Shrink table but don't want to shrink HWM (High Water Mark).

　　SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

　　3. Shrink table and HWM too.

　　SQL> ALTER TABLE scott.emp SHRINK SPACE;

　　4. Shrink table and all dependent index too.

　　SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

　　5. Shrink table under MView.

　　SQL> ALTER TABLE

　　SHRINK SPACE;

　　6. Shrink Index only.

　　SQL> ALTER INDEX SHRINK SPACE;

　　验证

　　SQL> set serveroutput on

　　SQL> declare

　　2 v_unformatted_blocks number;

　　3 v_unformatted_bytes number;

　　4 v_fs1_blocks number;

　　5 v_fs1_bytes number;

　　6 v_fs2_blocks number;

　　7 v_fs2_bytes number;

　　8 v_fs3_blocks number;

　　9 v_fs3_bytes number;

　　10 v_fs4_blocks number;

　　11 v_fs4_bytes number;

　　12 v_full_blocks number;

　　13 v_full_bytes number;

　　14 begin

　　15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,

　　16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

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

　　18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

　　19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

　　20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

　　21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

　　22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

　　23 dbms_output.put_line('Full Blocks = '||v_full_blocks);

　　24 end;

　　25 /

　　Unformatted Blocks = 0

　　FS1 Blocks = 0

　　FS2 Blocks = 0

　　FS3 Blocks = 0

　　FS4 Blocks = 2

　　Full Blocks = 1