您当前的位置: 首页 > 数据库教程 > Oracle教程 > 如何检查oracle表或索引可回收空间

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

作者:不详 来源:网络 发布时间: 2014-08-23 20:35 点击:
对表进行大量删除后,可能会有很多空闲空间可以回收,相关计算方法参考如下: 更新统计信息 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 D

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

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

  更新统计信息

  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
分享到:
本文"如何检查oracle表或索引可回收空间"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4