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