My Oracle Support Banner

Drop Table Does Not Remove The Blob LobIndex Segment (Doc ID 394442.1)

Last updated on AUGUST 08, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Information in this document applies to any platform.


Goal

How to remove the BLOB lobindex segment when dropping a table ?

SQL> CREATE TABLE t_lob
      (DOCUMENT_NR NUMBER(16,0) NOT NULL, 
       DOCUMENT_BLOB BLOB NOT NULL
      )
     TABLESPACE users 
     lob (DOCUMENT_BLOB) store as DOCUMENT_LOB
                        (tablespace example
                         index (tablespace test_user ) 
     );
Table created.
SQL> select * from dba_lobs where table_name='T_LOB';

OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS
------------------------------ ---------- ---------- ---------- ----------
CACHE LOGGING IN_ FORMAT PAR
---------- ------- --- --------------- ---
SYSTEM T_LOB
DOCUMENT_BLOB
DOCUMENT_LOB EXAMPLE

SYS_IL0000061291C00002$$ 8192 10 900
NO NO YES NOT APPLICABLE NO

SQL> select * from dba_segments
     where segment_name in ('T_LOB','DOCUMENT_LOB','SYS_IL0000061291C00002$$');

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
----------- ------------ ---------- ---------- ---------- --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
----------- ----------- ----------- ------------ ---------- ---------------
RELATIVE_FNO BUFFER_
------------ -------
SYSTEM
T_LOB
TABLE USERS
4 526355 65536 8 1 65536
1 2147483645
4 DEFAULT

SYSTEM
SYS_IL0000061291C00002$$
LOBINDEX EXAMPLE
5 491 65536 8 1 65536
1 2147483645
5 DEFAULT

SYSTEM
DOCUMENT_LOB
LOBSEGMENT EXAMPLE
5 483 65536 8 1 65536
1 2147483645
5 DEFAULT
SQL> drop table t_lob;
Table dropped.

SQL> select * from dba_segments
where segment_name in ('T_LOB', 'DOCUMENT_LOB','SYS_IL0000061291C00002$$');

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
----------- ------------ ---------- ---------- ---------- --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
----------- ----------- ----------- ------------ ---------- ---------------
RELATIVE_FNO BUFFER_
------------ -------
SYSTEM
SYS_IL0000061291C00002$$
LOBINDEX EXAMPLE
5 491 65536 8 1 65536
1 2147483645
5 DEFAULT

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.