检查oracle 表或索引可回收空间-凯发app官方网站

计算碎片空间
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
下载:

阅读(2252) | 评论(1) | 转发(0) |

上一篇：操作系统实用小命令

下一篇：linux oracle随机启动侦听和数据库所需参数修改

给主人留下些什么吧！~~

chinaunix网友2010-09-26 15:08:15

很好的, 收藏了推荐一个博客，提供很多免费软件编程电子书下载： http://free-ebooks.appspot.com

| | | | |

感谢所有关心和支持过chinaunix的朋友们