|
|
我们知道当我们对表进行大量的delete操作后,系统的性能会有明星的下降.
在10G以前我们是怎么做的呢?
1.exp/imp
2.alter table xxx move
10g提供一个新的功能
alter table tablenm shrink space
他可以释放表空间里的多余空间
他还适用于
Index
物理View
物理View log
◆环境
Linux 2.4.9-e.24enterprise
Oracle10g EE Release 10.1.0.2.0
◆Segment缩小命令
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'EMP';
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT EMP 53477376 6528 66
SQL> alter table emp shrink space;
ORA-10636: ROW MOVEMENT is not enabled
※Segment缩小命令必须开启行移动功能
SQL> alter table emp enable row movement;
Table altered.
SQL> alter table emp shrink space;
Table altered.
SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'EMP';
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS
----- ------------ ---------- ---------- ----------
SCOTT EMP 65536 8 1
如果是这样的话和alter table xxx move的功能基本上还是一样的,他必定有更强大的地方
◆alter table xxx shrink space VS. alter table xxx move
◇1. 在线的shrink
SES1>alter table emp move;
SES2>select l.oracle_username,o.name objname,l.locked_mode from v$locked_object l,obj$ o where l.object_id=o.obj#;
ORACLE_USERNAME OBJNAME LOCKED_MODE
------------------------------ ------------------------------ -----------
SCOTT EMP 6
SES2>select rownum from scott.emp where rownum=1 for update nowait;
ORA-00054:
SES1> alter table emp shrink space;
SES2> select l.oracle_username,o.name objname,l.locked_mode from v$locked_object l,obj$ o
where l.object_id=o.obj#;
ORACLE_USERNAME OBJNAME LOCKED_MODE
------------------------------ ------------------------------ -----------
SCOTT EMP 3
SES2>select rownum from scott.emp where rownum=1 for update nowait;
ROWNUM
----------
1
shrink比起move最大的不同是在object上没有排他锁.可以从LOCKED_MODE列上看出,在Move命令里该值是6,即排他锁而在shrink命令里该值是3,行级锁所以在不停止业务的情况下可以对表进行重组
◇2 shrink命令执行途中即使被强行终止,也可以完成一部分的空间整理
▽dbms_space.space_usage 整理前输出
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 16
0 - 25% free blocks= 0
25- 50% free blocks= 6366
50- 75% free blocks= 0
75-100% free blocks= 36
Full Blocks = 0
▽整理中终止
SQL> alter table emp shrink space;
ORA-00028: your session has been killed
▽强行终止后,表的状态
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 16
0 - 25% free blocks= 1
25- 50% free blocks= 2808
50- 75% free blocks= 0
75-100% free blocks= 1004
Full Blocks = 2553
▽再次使用shrink,并正常完成
Segment Owner = SCOTT
Segment Name = EMP
Unformatted Blocks = 0
0 - 25% free blocks= 1
25- 50% free blocks= 2
50- 75% free blocks= 0
75-100% free blocks= 0
Full Blocks = 4567
使用dbms_space.space_usage可以推算出表shrink的状况
1.整理前
没有Full Blocks,许多25%空的block
2.强行终止后
已经有Full Blocks,空的block在减少
3.正常完成后
free blocks没有了
适用表，索引，大对象，IOT，物化视图
alter table tbname row movement
保持HWM
alter table tbname shrink space compact;
回缩表与HWM
alter table tbname shrink space;
回缩表与相关索引
alter table tbname shrink space cascade;
回缩索引
alter index idxname shrink space;
限制
cluster中的表
有long类型的表
有on_commit物化视图的表
有基于rowid物化视图的表
大对象(LOB)索引
|
|
