shrinks a LOB segment

shrinks a LOB segment
http://www.itpub.net/thread-1034679-1-1.html

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

详细见如下测试:

PHP code:


第一种方法:



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





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

转载于:https://www.cnblogs.com/rootq/archive/2009/02/01/1381758.html