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

Bertha 。 2023-01-18 15:26 30阅读 0赞

![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中文网

[5268f80b9b1e01f982625ef6fac83ca1.png]: /images/20221021/72156c071b1d44fc83a387509a4b428f.png
[1428d0e076c3959ab11d28a39bc84fab.png]: /images/20221021/a6531135754641c789294917fa053d07.png