对表进行大量删除后，可能会有很多空闲空间可以回收，相关计算方法参考如下：
更新统计信息
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