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

Last updated on SEPTEMBER 02, 2016

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms