|
测试一：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; ----插入测试数据
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
-------尝试手工去回收lob 段空间
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 的版本中测试和上述现象一致。
总结：
在truncate table 时，如果表中含有lob 字段，相应的log segment 是不会被清理掉的，但是空间会被回收。
|