Drop Table Does Not Remove The Blob LobIndex Segment
(Doc ID 394442.1)
Last updated on JULY 05, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.2 and laterInformation 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 |