深入探讨Oracle数据库10g的Shrink机制 - 数据库编程

成功完成。

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

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

SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;

Table created

往table test_hwm中插入如下的数据：

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和信息：

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

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

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFHAAB 2 bb

AAANhqAAGAAAAFHAAC 2 cc

AAANhqAAGAAAAFIAAA 3 dd

AAANhqAAGAAAAFIAAB 4 ds

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

AAANhqAAGAAAAFJAAB 7 ess

AAANhqAAGAAAAFJAAC 8 es

AAANhqAAGAAAAFKAAA 9 es

AAANhqAAGAAAAFKAAB 10 es

11 rows selected

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name=TEST_HWM ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

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

0 6 6 324 5

1 6 6 329 5

然后从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的信息：