如何检查oracle 表或索引可回收空间 -pc6资讯

您的位置：首页 → 技术开发 → 数据库教程 → 如何检查oracle 表或索引可回收空间

如何检查oracle 表或索引可回收空间 时间：2011/2/12 11:04:56来源：本站原创作者：清晨我要评论(0)

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

更新统计信息
Analyze tablecompute 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 indexvalidate 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
部分内容如下：

<<--代码下载--->>

相关视频

没有数据

文章评论

查看所有0条评论>>

热门文章 oracle10g安装图解(wi