Test one: 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-----Initial Score With size
sys_lob0000085497c00001$$ lobsegment 65536
TTABLE 65536
sql> sql> insert INTO T select Lpad (segment_name,8192) from dba_segments; ----Inserting test data
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
TTABLE 327680
------TRUNCATE TABLE
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 The segment space has been freed, but the LOB segment still exists
TTABLE 65536
-------try to reclaim LOB segment space manually
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 segment still exists
sys_lob0000085497c00001$$ lobsegment 65536 ------LOB segments are still exists
TTABLE 65536
The version of Oracle 11g is tested in the same situation as above.
Summarize:
If the table contains LOB fields at TRUNCATE TABLE, the corresponding log segment will not be cleaned up, but the space will be reclaimed.
Does Truncate table remove the corresponding LOB segments for the tables at the same time? --Not
