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

时间：2010-09-23 来源：brjl

对表进行大量删除后，可能会有很多空闲空间可以回收，相关计算方法参考如下：更新统计信息
Analyze table <table_name> 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 <index name> 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 <table name> SHRINK SPACE; 6. Shrink Index only.
SQL> ALTER INDEX <index nam> 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