shrink space test

在 oracle11G中，如果表的内容被删除后想回收使用空间和降低HWM,我们可以通过shrink的方式在线完成.
那么如果表中有LOB字段时,也可以做吗?答案是肯定的,当然可以.
我们可以通过两种方法来做.一种是可以通过普通的alter table shrink space cascade来做(注意一定要有cascade),第二种是可以通过alter table lob (shrink space)来做,这样只回收LOB字段使用空间,不回收表的使用空间.

详细见如下测试:


第一种方法:

SQL> create table t_lob
  2  (id number primary key,
  3  txt clob)
  4  tablespace users
  5  lob(txt) store as(
  6  tablespace ts_lob
  7  disable storage in row
  8  )
  9  /  

Table created.

SQL> declare
  2  i number ;
  3  begin
  4  i:=1;
  5  for i in 1..50000 loop
  6  insert into t_lob values(i,'yxyupyxyupyxyupyxyupyxyupyxyupyxyupyxyupyxyupyxyupyxyupyxyupyxyupyxyup');
  7  end loop;
  8  commit;
  9  end;
  10 /

PL/SQL procedure successfully completed.


SQL> select segment_name,segment_type,bytes/1024/1024,tablespace_name from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ------------------ --------------- --------------------
T_LOB                          TABLE                            2 USERS
SYS_C006623                    INDEX                        .8125 USERS
SYS_LOB0000054209C00002$$      LOBSEGMENT                     400 TS_LOB
SYS_IL0000054209C00002$$       LOBINDEX                         5 TS_LOB

SQL> delete from t_lob where mod(id,2) = 0 ;

25000 rows deleted.

SQL> commit;

Commit complete.

SQL> select segment_name,segment_type,bytes/1024/1024,tablespace_name from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ------------------ --------------- --------------------
T_LOB                          TABLE                            2 USERS
SYS_C006623                    INDEX                        .8125 USERS
SYS_LOB0000054209C00002$$      LOBSEGMENT                     400 TS_LOB
SYS_IL0000054209C00002$$       LOBINDEX                         5 TS_LOB

SQL> alter table t_lob enable row movement;

Table altered.

SQL> alter table t_lob shrink space cascade;

Table altered.

SQL>  select segment_name,segment_type,bytes/1024/1024,tablespace_name from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ------------------ --------------- --------------------
T_LOB                          TABLE                         .875 USERS
SYS_C006623                    INDEX                           .5 USERS
SYS_LOB0000054209C00002$$      LOBSEGMENT                201.9375 TS_LOB
SYS_IL0000054209C00002$$       LOBINDEX                         5 TS_LOB

-----可以看出表和LOBSEGMENT都回收了使用空间.

第二种方法:

SQL> delete from t_lob where mod(id,3) = 0 ;

8333 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t_lob modify lob(txt) (shrink space) ;


Table altered.

SQL> SQL>
SQL>
SQL> select segment_name,segment_type,bytes/1024/1024,tablespace_name from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ------------------ --------------- --------------------
T_LOB                          TABLE                         .875 USERS
SYS_C006623                    INDEX                           .5 USERS
SYS_LOB0000054209C00002$$      LOBSEGMENT                  138.75 TS_LOB
SYS_IL0000054209C00002$$       LOBINDEX                         5 TS_LOB



SQL> alter table t_lob shrink space;

Table altered.

SQL> select segment_name,segment_type,bytes/1024/1024,tablespace_name from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ------------------ --------------- --------------------
T_LOB                          TABLE                        .5625 USERS
SYS_C006623                    INDEX                         .375 USERS
SYS_LOB0000054209C00002$$      LOBSEGMENT                   133.5 TS_LOB
SYS_IL0000054209C00002$$       LOBINDEX                         5 TS_LOB

-----可以看出LOBSEGMENT使用空间回收了,但是表的使用空间没有回收.只有对表shrink后才回收到 .5625


SQL> alter table t_lob disable row movement;

Table altered.

SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production



在测试过程中发现另一个问题.

就是不管怎么回收空间,那怕是把表给truncate掉了,LOB的LOBINDEX没有变化.

SQL> truncate table t_lob;

Table truncated.

SQL> select segment_name,segment_type,bytes/1024/1024,tablespace_name from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ------------------ --------------- --------------------
T_LOB                          TABLE                        .0625 USERS
SYS_C006623                    INDEX                         .375 USERS
SYS_LOB0000054209C00002$$      LOBSEGMENT                   133.5 TS_LOB
SYS_IL0000054209C00002$$       LOBINDEX                         5 TS_LOB

SQL> alter table t_lob shrink space cascade;

Table altered.

SQL>  select segment_name,segment_type,bytes/1024/1024,tablespace_name from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ------------------ --------------- --------------------
T_LOB                          TABLE                        .0625 USERS
SYS_C006623                    INDEX                        .0625 USERS
SYS_LOB0000054209C00002$$      LOBSEGMENT                   .0625 TS_LOB
SYS_IL0000054209C00002$$       LOBINDEX                         5 TS_LOB


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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24558279/viewspace-1129762/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24558279/viewspace-1129762/