检查oracle 表或索引可回收空间

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

 

 

更新统计信息
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

-------
碎片报表脚本
参考support.oracle.com 文档 ID 1019716.6

部分内容如下：

文件:	TFSLDTFR.rar
大小:	2KB
下载:	下载