Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Ji Sook.

Asked: November 14, 2001 - 1:20 am UTC

Last updated: February 28, 2024 - 5:42 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

I query table 'user_segments'
sql>select segment_name, segment_type, tablespace_name, bytes, max_extents
from user_segments
where segment_type like 'LOB%'

result is


=> I don't know what is lobsegement, lobindex.
What is lobsegment, lobindex and why need it.
Can't I delete it?
Please explain detail about lobsegement, lobindex.

Thank you.....

and Tom said...

You have a table with a LOB column in it.

A LOB is simply a pointer. It points to an index. the index points to the chunks that make up the LOB.

Hence when you create a LOB, you will always get a lob index created (to find the chunks for the lob fast) and a segment that holds the lob data (chunks).

You can only "delete" it by dropping the table or column that contains the LOB.


  (25 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Discussion on LOBs

Andre Whittick Nasser, November 14, 2001 - 9:57 am UTC

Getting back to our discussion at:

</code>,%7Blob%7D <code>

I was asking you about migrating VARCHAR2's to CLOBS, placing some (often mistaken) analogies.

I have this new piece of information from you I was not aware of before.

When I do:

a) CREATE TABLE t1 ( a VARCHAR2(4000) );


b) CREATE TABLE t2 ( a CLOB );

In a) what I understand is you are creating a VARCHAR2 field for every row ocurrence (with respective lenght data, etc.)

In b) what I see is that you have only one CLOB for the table and each CLOB field stores a pointer to the index, which in turn points to the chunks.


1) You have only one CLOB segment for every CLOB fields of any regular table ?

2) What about in-line CLOBS ? Do they really "share" the table blocks or continue to be separated segments ? What about pointers and indexes here ?

Tom Kyte
November 14, 2001 - 11:08 am UTC

1) you have A clob index and A clob segment for each CLOB column in a table.

2) inline clobs, the ability to "inline" the data in original block -- if the clob is less than about 4,000 bytes -- will still have an index and lob segment since the moment they exceed the threshold -- they need SOMEWHERE to go. Hence even inlined CLOBS have the index and segment.


Andre Whittick Nasser, November 14, 2001 - 12:11 pm UTC

Sorry Tom,

This discussion on LOBs is getting very long, probably I miss some points. I have the Oracle University books, but they lack a bit of consistency. Would you please suggest me some good material, peferrably on line ?

Tom Kyte
November 14, 2001 - 1:12 pm UTC

Renaming LOB INDEX

Mohan K, March 07, 2003 - 12:52 am UTC

I was able to give my own name for LOB SEGMENT in create table statement. I want to rename the LOBINDEX segment and give a meaningful name instead of SYS_IL0000003838C00006$$. Please tell me how to do it.

Tom Kyte
March 07, 2003 - 7:58 am UTC

you cannot do it.

As a famous movie once said

"ignore that man behind the curtain".


yazid, January 23, 2004 - 5:29 am UTC

Hi tom,
As I see the lobsegment and the lobindex are always stored in the same tablespace.
Can i store them in diffrent tablespace : for example store lobsegment in LOB_DATA and lonindex in LOB_INDEX.

What's better store them in the same tablespace or separate there storage.

I tried to execute this statement but both the lobsegment and the lobindex are in the same tablespace !!
( TABLESPACE edeclic_index
STORAGE ( maxextents 8192 )

scott@test8i> r
1 select segment_name, initial_extent, next_extent, max_extents,TABLESPACE_NAME from user_segments
2* where segment_name like 'SAB_%'

------------------------- -------------- ----------- ----------- -------------------------
SAB_XML_PERSIST 131072 131072 2147483645 EDECLIC_DATA
SAB_LOB_DATA_XML_PERSIST 131072 131072 2147483645 EDECLIC_DATA

Why the lobindex doesn't use EDECLIC_INDEX segment ?


Tom Kyte
January 23, 2004 - 8:05 am UTC

you control the placement of the lob  segment.

you have NO control over the lob index, it is an internal structure and goes where it goes.  the lob index is considered "part of the lob" itself.

ops$tkyte@ORA9IR2> CREATE TABLE "T"
  2  ("X" NUMBER(*,0),
  3   "Y" CLOB)
  6  /
Table created.
ops$tkyte@ORA9IR2> select segment_name, tablespace_name from user_segments;
------------------------------ ------------------------------
T                              T1_DATA
SYS_IL0000034510C00002$$       T2_DATA
SYS_LOB0000034510C00002$$      T2_DATA

the syntax you are using is 

a) deprecated
b) ignored

by design. 

A reader, January 23, 2004 - 12:49 pm UTC


Pradikan, November 05, 2005 - 5:04 am UTC

Hi Tom,

I've a typical Situation. In one Database I am handling we have a LOBSEGMENT and we found that the max_extents of this lobsegment has reached 95%. I tried to increase the maxtents by altering this LOB and surprised to see that we cannot alter a LOBSEGMENT. I tried some other ways but could not change the max extents for the same.

Please let me know can this be done, If Yes then direct me Else is there any alternative.

The table description is as follows

 Name                               Null?    Type
 ------------------- -------- ----------------------------
 ITEM_TYPE                         NOT NULL VARCHAR2(8)
 ITEM_KEY                          NOT NULL VARCHAR2(240)
 NAME                              NOT NULL VARCHAR2(30)
 TEXT_VALUE                        VARCHAR2(4000)
 NUMBER_VALUE                      NUMBER
 DATE_VALUE                        DATE
 EVENT_VALUE                       APPS.WF_EVENT_T
 SECURITY_GROUP_ID                 VARCHAR2(32)

The column is EVENT_VLAUE.

Thanks in Advance.



Tom Kyte
November 05, 2005 - 5:49 pm UTC

not sure I exactly know what you mean by "max_extents of this lobsegment has reached 95%"

do you mean it is in a dictionary managed tablespace and you have allocated 95% of the possible extents you are permitted?

What exactly failed, what command failed?


Pradikan, November 07, 2005 - 3:11 am UTC

Hi Tom,

I think I was not clear in explaining the problem. The error we got was

DBSPI-0217.1: Extents to maximum extents percentage (90.11%) too high for SYS_LOB0000034896C00015$$ in database s761 (>=90.00%).

So i tried to increse the maximum extents, but could not do it. I need your help in acheiving the same.

Thanks & Regards


Tom Kyte
November 07, 2005 - 8:51 am UTC

what is DBSPI? This is not a message I am familar with, what is it, what does it mean and what generated it?

what is the "maximum extents percentage".

In line LOBS

Ik, December 14, 2005 - 10:02 am UTC


About inline LOBS -

Once Oracle determines the table ROWIDs it is going to fetch, it reads each block into the buffer. Now, this block would contain the LOB data also - inline.

Now, LOB contains a pointer to the index. And index contains pointer to the chunk - which points back to the same block. Is that correct?

My question (1) is this correct?

(2) What sort of wait events get logged for LOBINDEX related waits 'db_file_sequential_reads' ?

(3) would the records containing LOB be accessed row-by-row irrespective of the client arraysize?


Tom Kyte
December 14, 2005 - 10:08 am UTC

inline lobs are inline, there isn't a need to leave the block.

lobindexes are read just like regular indexes.

the thing is the client gets a LOB LOCATOR which they send back to the server typically and then the server figures out where the lob data is and sends that back to the client.

Still confused though

JM, December 14, 2005 - 12:40 pm UTC

My understanding of lob is lob stores an index value that actually points to another index pointing to the chunk location.

If my understanding is correct, then why can't we store 2nd index in the lob column rather the index to the index?

Tom Kyte
December 14, 2005 - 12:59 pm UTC

... then why can't we store 2nd index in the lob
column rather the index to the index? ...

that didn't make sense to me, not sure what you are trying to say. "what 2nd index"?

the lob locator is used by the server to find the lob, it might include the "key" value to the first chunk in a lob segment (found via the lob index stored with the lob segment)

Still confused though :-)

JM, December 17, 2005 - 10:47 am UTC

when I said 2nd index, i was refering to "key" value mentioned in your response. i assume "key" represents a pointer to a data segment.

my understanding again is:

To retrieve lob data, server goes to the lob column. picks up the value in that column. this data is a pointer to pointer (2nd pointer) that points to the location of lob data (actual).

so the question is where is the need for this redirection?

Did not complete the previous post...Sorry.

JM, December 17, 2005 - 10:52 am UTC

sorry. I do not think i finished my explanation.

hen I said 2nd index, i was refering to "key" value mentioned in your response.
i assume "key" represents a pointer to a data segment.

my understanding again is:

To retrieve lob data, server goes to the lob column. picks up the value (value1) in that column. using value1, server goes to the location pointed to by value1 and picks the value (value2) stored in that location. using value2 (pointer again) server goes to the location where actual lob data is stored and retrieves it.

if this understanding is correct, so the question is where is the need for this redirection?

Tom Kyte
December 17, 2005 - 11:20 am UTC

lobs are stored in chunks.

a single lob may have 0, 1 or many chunks all over the place.

These chunks are pointed to by the lob index.

The lob locator points to the "first entry" in the lob index, the lob index points to the chunks. To retrieve the entire lob we "range scan" the lob index getting the address of the chunks we need.

Nice explanation

JM, December 17, 2005 - 3:54 pm UTC

Great. Now I understand. Thanks for succinct explanation.

How to find which table uses which LOBSEGMENT?

A reader, July 21, 2006 - 5:52 pm UTC

Is there a query that can query some DB dictionary to find what table uses which LOGSEGMENT?

Tom Kyte
July 23, 2006 - 7:59 am UTC

desc dba_lobs

How to re-org

reader, August 16, 2006 - 4:48 am UTC

SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='TFS'
  2  order by 3 DESC ;

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_LOB0000031864C00007$$      LOBSEGMENT              100003.906

Is there a way I can rebuild this LOBSEGMENT or see if space is being wasted ?


Tom Kyte
August 16, 2006 - 8:36 am UTC

you can MOVE a lob as part of an alter table T move. that rebuilds it (and the table and hence you have to rebuild all indexes on said table)

Which is better Move or create ?

Reader, August 17, 2006 - 7:38 am UTC

Thank you for your quick response.

Which is a better option --

1.exp with rows=no to get the script
then rename the table with lob
create table (CTAS) as select * from old table .
Rebuild all indexes

2.OR Move
Then Rebuild all indexes .


Tom Kyte
August 17, 2006 - 9:18 am UTC

move certainly seems less complex doesn't it.

LOBSEGMENT not disappear after main table deleted

joseph, April 15, 2008 - 1:10 am UTC

Hi Tom

Thank you for all the efforts and the info. I have a follow-up question.

You were saying ...

"You can only "delete" it by dropping the table or column that contains the LOB."

I created a LOB table - TEMP_LOB5. After I DROPPED this table, the USER_SEGMENT showed a segment BIN$SuKj2gDvQODgQwoNagpA4A==$0. It seems that TEMP_LOB5 became this table(size is the same), and the lobsegment did not disappear and still there occupying space. Every time I dropped a table containing a LOB column, a table wtih BIN$... showed up.

What is the cause of this?

thanks so much
Tom Kyte
April 16, 2008 - 2:31 pm UTC

that is called the recycle bin

It is normal
it is correct
it is supposed to happen that way

so you can "undrop" that table

the space is "free", we can reuse it, we just have it marked so we can un-drop it later if you ask us to.

if you do "drop table T purge", the recycle bin won't happen - I don't recommend that, the space is in fact FREE TO BE USED again - and as long as objects are in the recycle bin, you can "un-drop them"

LOB Segment Index - Number of Extent too high

Manoj, September 02, 2008 - 10:54 pm UTC

Hi Tom,

I have a LOB segment defined on my table T,

SQL> desc T;
 Name                     Null?    Type
 --------------------------- -------- ------------------------------------
 OBJID                   NOT NULL NUMBER
 DEV                              NUMBER
 NAME                             VARCHAR2(20)
 VALUE                            CLOB
  C5                 NUMBER
 C6              NUMBER

SQL> col COLUMN_NAME for a20
SQL> select table_name,column_name,index_name from user_lobs where table_name='T';

-------------------------    --------------------  ------------------------------
T                 VALUE                SYS_IL0000061891C00004$$

SQL> select count(*) from user_extents where segment_name='SYS_LOB0000061891C00004$$';


SQL> select count(*) from user_extents where segment_name='T';


SQL> select  initial_extent,next_extent from user_tables where table_name='T';

-------------- -----------
         65536    10485760


LOB segement has same initial extent/next extent values defined as defined for table T.

I have questions
i) High number of LOB index extents will cause any performance impact?
ii) Can i change the storage parameter for the LOB segment  , so that we can control the number of extents for LOB index?
(setting Initial/next extent size high, We are using LMT tablespace.) 

Thanks for your time.


Tom Kyte
September 03, 2008 - 10:35 am UTC

i) no, but you might consider looking into locally managed tablespaces with system allocated extents for everything you do in the future.

ii) you give no information about the lmt - if you are using system allocated extents, you are done - it is good, leave it be, no worries. If you are using uniform extent sizes (not recommended by me), then you have a uniform size, you'd have to reorganize the structure into another tablespace.

Many Thanks

Manoj Kaparwan, September 06, 2008 - 2:49 am UTC

Hi Tom,

Many thanks for helping out the entire Oracle Community across the world.

Sorry, I missed to gave the information on lmt. We have a mix of System allocated extents and uniform extents tablespaces.So we will be reorganising the table into another tablespace which has extents system allocated.

Changing Tablespace to System Allocated extents from Uniform extent sizes.

Manoj, October 02, 2008 - 9:01 am UTC

Hi Tom,

Thanks for your time.

...system allocated extents, you are done - it is good, leave it be, no worries. If you are using uniform extent sizes (not recommended by me), then you have a uniform size, you'd have to reorganize the structure into another tablespace.

Can we change the tablespace with uniform extent sizes to System Allocated extents, with tablespace holding the objects?

Tom Kyte
October 02, 2008 - 9:07 am UTC

no, you cannot change it - you would create a new one and move the segments into it.

A reader, April 06, 2011 - 3:42 am UTC

what is lobsegment,lobindex", version 8.1.7

SK VERMA, April 06, 2011 - 3:44 am UTC

Very useful document. The explaination given by Tom are just upto the mark. Very clear and very straight forward. Everything is clear after reading the doc.


Renaming a LOB index

Deb Joyce, July 02, 2011 - 8:55 am UTC

You can rename a lob segment name as follows:
ALTER TABLE table_name MOVE LOB (column_name) STORE AS segment_name (TABLESPACE tablepace_name);
Tom Kyte
July 05, 2011 - 7:50 am UTC

I'm not sure I'd call an entire rebuild of a table, all of it's indexes and a log segment and index a simple "rename"

It would be like using an atom bomb to crack a walnut open. If you do this - be prepared to rebuild the indexes on table TABLE_NAME as well afterwards as they'll all be invalid.

A reader, September 24, 2012 - 5:38 am UTC

I've 3 short questions.

If I've a lobsegment name like 'SYS_LOB0000083667C00029$$' then:

a) how can i find that originally to which table it refers to?
b) what is the lob column's original name?
c) while shrinking lob segment, do we have to provide "SYS_LOB0000083667C00029$$' or actual column name?
Tom Kyte
September 27, 2012 - 7:55 am UTC

ops$tkyte%ORA11GR2> create table t ( x clob );

Table created.

ops$tkyte%ORA11GR2> select table_name, column_name, segment_name from user_lobs;



Table altered.

query user_lobs by segment name.

you reference the lob column name to shrink it.

size of lob

A reader, August 12, 2016 - 7:06 am UTC

i create a table and a column contain one lob datatype.
now i find out size of the lob column as below

select nvl((sum(dbms_lob.getlength('test_lob'))),0) as bytes from test_lob;

output is 10000 bytes.
but i confused my table segment contain a 2 gb data.
Connor McDonald
August 14, 2016 - 5:36 am UTC

a) Is it possible that you *once* had 2G worth of data in there ?
b) How are you determining that 2G ?

Naming and Renaming LOB Segments and LOB Indexes

Al Garrison, February 27, 2024 - 2:33 pm UTC

There is only one way to name a LOB Index that I am aware of. You must name the LOB index during table creation. You can change the name of a LOB Segment using the alter table move command. But even though the syntax will allow it, Oracle will not rename an existing LOB index. NOTE: Oracle 23c has enhanced LOB maintenance. In 23c, Oracle introduces the alter table rename command (for LOBs).

Create Example:

create table tt
one number,
two clob
lob (TWO) store as basicfile TWO
tablespace HINDX
enable storage in row
chunk 8192
index TWO_IDX
tablespace HINDX
) tablespace HDATA;

The same basic syntax works for alter table move except, like I stated previously, you cannot rename the LOB Index.

alter table tt move lob (TWO) store as basicfile TT_TWO (tablespace OBJS index TT_TWO_IDX (tablespace HINDX)) tablespace HDATA;

This moves the table (data) into tablespace HDATA. And it moves the LOB Segment (TT_TWO) [after renaming it from TWO to TT_TWO] AND LOB Index (TWO_IDX) into the OBJS tablespace. Note: the index did NOT get renamed to TT_TWO_IDX.

Connor McDonald
February 28, 2024 - 5:42 am UTC

LOB segment rename comes in 23c.