Truncate table 會(huì)同時(shí)刪除表相應(yīng)的LOB 段嗎？--不會(huì)
來源：程序員人生 發(fā)布時(shí)間：2015-03-16 10:39:02 閱讀次數(shù)：3529次
測(cè)試1：rhel5 ；ora10203
SQL> create user lixora identified by lixora default tablespace users;
User created.
SQL> grant dba to lixora;
Grant succeeded.
SQL> conn lixora/lixora
Connected.
SQL> select count(*) from user_segments;
COUNT(*)
----------
0
SQL> create table t(x clob);
Table created.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
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
SQL> select segment_name, segment_type, bytes
2 from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES
--------------------------------------------------------------------------------- ------------------ ----------
SYS_IL0000085497C00001$$ LOBINDEX 65536 -----初始分配大小
SYS_LOB0000085497C00001$$ LOBSEGMENT 65536
T
TABLE 65536
SQL> SQL> insert into t select lpad(segment_name,8192) from dba_segments; ----插入測(cè)試數(shù)據(jù)
4780 rows created.
SQL> commit;
Commit complete.
SQL> select segment_name, segment_type, bytes
2 from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES
--------------------------------------------------------------------------------- ------------------ ----------
SYS_IL0000085497C00001$$ LOBINDEX 65536
SYS_LOB0000085497C00001$$ LOBSEGMENT 40894464
T
TABLE 327680
------truncate 表
SQL> truncate table t;
Table truncated.
SQL> select segment_name, segment_type, bytes
2 from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES
--------------------------------------------------------------------------------- ------------------ ----------
SYS_IL0000085497C00001$$ LOBINDEX 65536
SYS_LOB0000085497C00001$$ LOBSEGMENT 65536 -------lob 段空間已釋放了，但lob 段依然存在
T
TABLE 65536
-------嘗試手工去回收l(shuí)ob 段空間
SQL> alter table t modify lob(x) (shrink space);
Table altered.
SQL> select segment_name, segment_type, bytes
2 from user_segments;
SEGMENT_NAME SEGMENT_TYPE BYTES
--------------------------------------------------------------------------------- ------------------ ----------
SYS_IL0000085497C00001$$ LOBINDEX 65536 -------lob 段依然存在
SYS_LOB0000085497C00001$$ LOBSEGMENT 65536 ------lob 段依然存在
T
TABLE 65536
oracle 11g 的版本中測(cè)試和上述現(xiàn)象1致。
總結(jié)：
在truncate table 時(shí)，如果表中含有l(wèi)ob 字段，相應(yīng)的log segment 是不會(huì)被清算掉的，但是空間會(huì)被回收。
生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
