oracle自己会回收索引碎片吗,如何检查oracle表或索引可回收空间

5268f80b9b1e01f982625ef6fac83ca1.png

对表进行大量删除后,可能会有很多空闲空间可以回收,相关计算方法参考如下: 更新统计信息 Analyze table compute statistics ; 计算碎片空间 SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024) Data lower than HWM in

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

更新统计信息

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 ,

1428d0e076c3959ab11d28a39bc84fab.png

5268f80b9b1e01f982625ef6fac83ca1.png

本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源:php中文网

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值