程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> 如何檢查oracle表或索引可回收空間

如何檢查oracle表或索引可回收空間

編輯:關於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

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved