Oracle 10g Shrink Table 详解［表空间收缩］

转自：http://blog.csdn.net/tianlesoftware/archive/2009/11/04/4764254.aspx

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。

有关ASSM的详细信息，请参考我的Blog：Oracle 自动段空间管理

http://blog.csdn.net/tianlesoftware/archive/2009/12/07/4958989.aspx

如果经常在表上执行DML操作，会造成数据库块中数据分布稀疏，浪费大量空间。同时也会影响全表扫描的性能，因为全表扫描需要访问更多的数据块。从oracle10g开始，表可以通过shrink来重组数据使数据分布更紧密，同时降低HWM释放空闲数据块。

segment shrink分为两个阶段：

1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。

2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

shrink space语句两个阶段都执行。

shrink space compact只执行第一个阶段。

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

注意：alter table XXX enable row movement语句会造成引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。

语法：

alter table <table_name> shrink space [ <null> | compact | cascade ];

alter table <table_name> shrink space compcat;

收缩表，相当于把块中数据打结实了，但会保持 high water mark;

alter table <tablespace_name> shrink space;

收缩表，降低 high water mark;

alter table <tablespace_name> shrink space cascade;

收缩表，降低 high water mark，并且相关索引也要收缩一下下。

alter index idxname shrink space;

回缩索引

1：普通表

Sql脚本，改脚本会生成相应的语句

select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;

select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;

2：分区表的处理

进行shrink space时发生ORA-10631错误.shrink space有一些限制.

在表上建有函数索引（包括全文索引）会失败。

Sql脚本，改脚本会生成相应的语句

select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;

select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;

select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';

详细测试：

我们用系统视图all_objects来在上个测试的tablespace ASSM上创建测试表my_objects

/* Formatted on 2009-12-7 20:42:45 (QP5 v5.115.810.9015) */

CREATE TABLESPACE ASSM DATAFILE 'd:\ASSM01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

/* Formatted on 2009-12-7 20:39:26 (QP5 v5.115.810.9015) */

SELECT TABLESPACE_NAME,

BLOCK_SIZE,

EXTENT_MANAGEMENT,

ALLOCATION_TYPE,

SEGMENT_SPACE_MANAGEMENT

FROM dba_tablespaces

WHERE TABLESPACE_NAME = 'ASSM';

TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN

--------------------- ---------- ---------- --------- ------

ASSM 8192 LOCAL SYSTEM AUTO

1 row selected.

/* Formatted on 2009-12-7 20:44:15 (QP5 v5.115.810.9015) */

CREATE TABLE my_objects

TABLESPACE assm

SELECT * FROM all_objects;

然后我们随机地从table MY_OBJECTS中删除一部分数据：

SQL> SELECT COUNT ( * ) FROM my_objects;

COUNT(*)

----------

49477

SQL> delete from my_objects where object_name like '%C%';

SQL> delete from my_objects where object_name like '%U%';

SQL> delete from my_objects where object_name like '%A%';

现在我们使用show_space()来看看my_objects的数据存储状况：

注： show_space() 存储过程代码参看一下连接的附件

http://blog.csdn.net/tianlesoftware/archive/2009/12/07/4958989.aspx

SQL>exec show_space('my_objects','auto','T','Y');

Total Blocks............................768

Total Bytes.............................6291456

Unused Blocks...........................68

Unused Bytes............................557056

Last Used Ext FileId....................8

Last Used Ext BlockId...................649

Last Used Block.........................60

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............41

0% -- 25% free space bytes..............335872

25% -- 50% free space blocks............209

25% -- 50% free space bytes.............1712128

50% -- 75% free space blocks............190

50% -- 75% free space bytes.............1556480

75% -- 100% free space blocks...........229

75% -- 100% free space bytes............1875968

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................11

Total bytes.............................90112

PL/SQL 过程已成功完成。

这里，table my_objects的HWM下有767个block，其中，free space为25-50%的block有209个，free space为50-75%的block有190个，free space为75-100%的block有229个. Total blocks 11个。

这种情况下，我们需要对这个table的现有数据行进行重组。

要使用assm上的shink，首先我们需要使该表支持行移动，可以用这样的命令来完成：

alter table my_objects enable row movement;

现在，就可以来降低my_objects的HWM，回收空间了，使用命令：

alter table bookings shrink space;

我们具体的看一下实验的结果：

SQL> alter table my_objects enable row movement;

表已更改。

SQL> alter table my_objects shrink space;

表已更改。

SQL>exec show_space('my_objects','auto','T','Y');

Total Blocks............................272

Total Bytes.............................2228224

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................8

Last Used Ext BlockId...................265

Last Used Block.........................16

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............1

50% -- 75% free space bytes.............8192

75% -- 100% free space blocks...........0

75% -- 100% free space bytes............0

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................257

Total bytes.............................2105344

在执行玩shrink命令后，我们可以看到，table my_objects的HWM现在降到了271的位置，而且HWM下的block的空间使用状况，Total blocks 的block有257个，free space 为25-50% Block只有0个。

Shrink 的实现机制：

我们接下来讨论一下shrink的实现机制，我们同样使用讨论move机制的那个实验来观察。

/* Formatted on 2009-12-7 20:58:40 (QP5 v5.115.810.9015) */

CREATE TABLE TEST_HWM (id INT, name CHAR (2000))

TABLESPACE ASSM;

INSERT INTO TEST_HWM VALUES (1, 'aa');

INSERT INTO TEST_HWM VALUES (2, 'bb');

INSERT INTO TEST_HWM VALUES (2, 'cc');

INSERT INTO TEST_HWM VALUES (3, 'dd');

INSERT INTO TEST_HWM VALUES (4, 'ds');

INSERT INTO TEST_HWM VALUES (5, 'dss');

INSERT INTO TEST_HWM VALUES (6, 'dss');

INSERT INTO TEST_HWM VALUES (7, 'ess');

INSERT INTO TEST_HWM VALUES (8, 'es');

INSERT INTO TEST_HWM VALUES (9, 'es');

INSERT INTO TEST_HWM VALUES (10, 'es');

我们来看看这个table的rowid和block的ID和信息：

/* Formatted on 2009-12-7 21:00:02 (QP5 v5.115.810.9015) */

SQL>SELECT ROWID, id, name FROM TEST_HWM;ROWID ID NAME

ROWID ID NAME

------------------------------------- ---------- --------

AAANMEAAIAAAAEcAAA 3 dd

AAANMEAAIAAAAEcAAB 4 ds

AAANMEAAIAAAAEcAAC 5 dss

AAANMEAAIAAAAEdAAA 6 dss

AAANMEAAIAAAAEdAAB 7 ess

AAANMEAAIAAAAEdAAC 8 es

AAANMEAAIAAAAEeAAA 9 es

AAANMEAAIAAAAEeAAB 10 es

AAANMEAAIAAAAEgAAA 1 aa

AAANMEAAIAAAAEgAAB 2 bb

AAANMEAAIAAAAEgAAC 2 cc

/* Formatted on 2009-12-7 21:00:49 (QP5 v5.115.810.9015) */

SQL>SELECT EXTENT_ID,

FILE_ID,

RELATIVE_FNO,

BLOCK_ID,

BLOCKS

FROM dba_extents

WHERE segment_name = 'TEST_HWM';

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 8 8 281 8

1 row selected.

然后从table test_hwm中删除一些数据：

delete from TEST_HWM where id = 2;

delete from TEST_HWM where id = 4;

delete from TEST_HWM where id = 3;

delete from TEST_HWM where id = 7;

delete from TEST_HWM where id = 8;

观察table test_hwm的rowid和blockid的信息：

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

------------------------------------------ ---------- ---------

AAANMEAAIAAAAEcAAC 5 dss

AAANMEAAIAAAAEdAAA 6 dss

AAANMEAAIAAAAEeAAA 9 es

AAANMEAAIAAAAEeAAB 10 es

AAANMEAAIAAAAEgAAA 1 aa

/* Formatted on 2009-12-7 21:00:49 (QP5 v5.115.810.9015) */

SQL>SELECT EXTENT_ID,

FILE_ID,

RELATIVE_FNO,

BLOCK_ID,

BLOCKS

FROM dba_extents

WHERE segment_name = 'TEST_HWM';

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 8 8 281 8

1 row selected.

从以上的信息，我们可以看到，在table test_hwm中，剩下的数据是分布在AAAAEc，AAAAEd，AAAAEf，AAAAEg这样四个连续的block中。

SQL> exec show_space('TEST_HWM','auto','T','Y');

Total Blocks............................8

Total Bytes.............................65536

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................8

Last Used Ext BlockId...................281

Last Used Block.........................8

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free space bytes.............8192

50% -- 75% free space blocks............3

50% -- 75% free space bytes.............24576

75% -- 100% free space blocks...........1

75% -- 100% free space bytes............8192

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................0

Total bytes.............................0

我们可以看到目前这四个block的空间使用状况，AAAAEc，AAAAEd，AAAAEf，AAAAEg上各有一行数据，我们猜测free space为50-75%的3个block是这三个block，那么free space为25-50%的1个block就是AAAAEg了，剩下free space为 75-100% 的3个block，是HWM下已格式化的尚未使用的block。（在extent不大于于16个block时，是以一个extent为单位来移动的）

然后，我们对table my_objects执行shtink的操作：

SQL> alter table test_hwm enable row movement;

Table altered

SQL> alter table test_hwm shrink space;

Table altered

SQL> select rowid ,id,name from TEST_HWM;

ROWID ID NAME

------------------ ---------- ------------

AAANMEAAIAAAAEcAAA 10 es

AAANMEAAIAAAAEcAAC 5 dss

AAANMEAAIAAAAEcAAD 1 aa

AAANMEAAIAAAAEcAAE 9 es

AAANMEAAIAAAAEdAAA 6 dss

/* Formatted on 2009-12-7 21:00:49 (QP5 v5.115.810.9015) */

SQL>SELECT EXTENT_ID,

FILE_ID,

RELATIVE_FNO,

BLOCK_ID,

BLOCKS

FROM dba_extents

WHERE segment_name = 'TEST_HWM';

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

---------- ---------- ------------ ---------- ----------

0 8 8 281 8

1 row selected.

当执行了shrink操作后，有意思的现象出现了。我们来看看oracle是如何移动行数据的，这里的情况和move已经不太一样了。我们知道，在move操作的时候，所有行的rowid都发生了变化，table所位于的block的区域也发生了变化，但是所有行物理存储的顺序都没有发生变化，所以我们得到的结论是，oracle以block为单位，进行了block间的数据copy。那么shrink后，我们发现，部分行数据的rowid发生了变化，同时，部分行数据的物理存储的顺序也发生了变化，而table所位于的block的区域却没有变化，这就说明，shrink只移动了table其中一部分的行数据，来完成释放空间，而且，这个过程是在table当前所使用的block中完成的。

那么Oracle具体移动行数据的过程是怎样的呢？我们根据这样的实验结果，可以来猜测一下：

Oracle是以行为单位来移动数据的。Oracle从当前table存储的最后一行数据开始移动，从当前table最先使用的block开始搜索空间，所以，shrink之前，rownum＝10的那行数据（10,es），被移动到block AAAAEc上，写到（1,aa）这行数据的后面，所以（10,es）的rownum和rowid同时发生改变。然后是（9,es）这行数据，重复上述过程。这是oracle从后向前移动行数据的大致遵循的规则，那么具体移动行数据的的算法是比较复杂的，包括向ASSM的table中insert数据使用block的顺序的算法也是比较复杂的，大家有兴趣的可以自己来研究，在这里我们不多做讨论。

在shrink table的同时shrink这个table上的index：

alter table my_objects shrink space cascade;

同样地，这个操作只有当table上的index也是ASSM时，才能使用。

Move 和 Shrink 产生日志的对比

我们对比了同样数据量和分布状况的两张table，在move和shrink下生成的redo size（table上没有index的情况下）：

/* Formatted on 2009-12-7 21:20:43 (QP5 v5.115.810.9015) */

SQL>SELECT tablespace_name, SEGMENT_SPACE_MANAGEMENT

FROM dba_tablespaces

WHERE tablespace_name IN ('ASSM', 'HWM');

TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT

------------------------------ ------------------------

ASSM AUTO

HWM MANUAL

SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;

Table created

SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000;

Table created

SQL> select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS';

BYTES/1024/1024

---------------

2.1875

SQL> delete from my_objects where object_name like '%C%';

7278 rows deleted

SQL> delete from my_objects1 where object_name like '%C%';

7278 rows deleted

SQL> delete from my_objects where object_name like '%U%';

2732 rows deleted

SQL> delete from my_objects1 where object_name like '%U%';

2732 rows deleted

SQL> commit;

Commit complete

SQL> alter table my_objects enable row movement;

Table altered

/* Formatted on 2009-12-7 21:21:48 (QP5 v5.115.810.9015) */

SQL>SELECT VALUE

FROM v$mystat, v$statname

WHERE v$mystat.statistic# = v$statname.statistic#

AND v$statname.name = 'redo size';

VALUE

----------

27808792

SQL> alter table my_objects shrink space;

Table altered

SQL>SELECT VALUE

FROM v$mystat, v$statname

WHERE v$mystat.statistic# = v$statname.statistic#

AND v$statname.name = 'redo size';

VALUE

----------

32579712

SQL> alter table my_objects1 move;

Table altered

SQL>SELECT VALUE

FROM v$mystat, v$statname

WHERE v$mystat.statistic# = v$statname.statistic#

AND v$statname.name = 'redo size';

VALUE

----------

32676784

对于table my_objects，进行shrink，产生了32579712 – 27808792=4770920，约4.5M的redo ；对table my_objects1进行move，产生了32676784-32579712= 97072,约95K的redo size。

结论：与move比较起来，shrink的日志写要大得多。

Shrink的几点问题：

1. shrink后index是否需要rebuild：

因为shrink的操作也会改变行数据的rowid，那么，如果table上有index时，shrink table后index会不会变为UNUSABLE呢？

我们来看这样的实验，同样构建my_objects的测试表：

create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;

create index i_my_objects on my_objects (object_id);

delete from my_objects where object_name like '%C%';

delete from my_objects where object_name like '%U%';

现在我们来shrink table my_objects：

SQL> alter table my_objects enable row movement;

Table altered

SQL> alter table my_objects shrink space;

Table altered

SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';

INDEX_NAME STATUS

------------------------------ --------

I_MY_OBJECTS VALID

我们发现，table my_objects上的index的状态为VALID，估计shrink在移动行数据时，也一起维护了index上相应行的数据rowid的信息。我们认为，这是对于move操作后需要rebuild index的改进。但是如果一个table上的index数量较多，我们知道，维护index的成本是比较高的，shrink过程中用来维护index的成本也会比较高。

2. shrink时对table的lock

在对table进行shrink时，会对table进行怎样的锁定呢？当我们对table MY_OBJECTS进行shrink操作时，查询v$locked_objects视图可以发现，table MY_OBJECTS上加了row-X (SX) 的lock：

SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;

OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE

---------- ---------- ------------------ -----------

55422 153 DLINGER 3

SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';

OBJECT_ID

----------

55422

那么，当table在进行shrink时，我们对table是可以进行DML操作的。

3． shrink对空间的要求

我们在前面讨论了shrink的数据的移动机制，既然oracle是从后向前移动行数据，那么，shrink的操作就不会像move一样，shrink不需要使用额外的空闲空间。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/tianlesoftware/archive/2009/11/04/4764254.aspx

posted on 2011-04-15 10:57 xzc 阅读(9343) 评论(0) 编辑收藏所属分类: Oracle

常用链接

留言簿(12)

随笔分类

随笔档案

收藏夹

搜索

最新评论

阅读排行榜

评论排行榜


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 Chat2DB C++博客博问管理
相关文章: Oracle to_char格式化函数关于ociuldr ORACLE确定当前记录和下一条记录之间的差值 SYS_CONNECT_BY_PATH函数用法 ORACLE Windows Oracle 10G手工创建数据库谈谈WINDOWS下重装系统后oracle的恢复 DML、DDL、DCL区别 oracle 10g 正则表达式　REGEXP_LIKE Oracle 字符集的查看和修改 Oracle 10g Shrink Table 详解［表空间收缩］