Getting back to our discussion at:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1465222650804,%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) );

and

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.

Questions:

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 ?

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 ?

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.

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 !!
CREATE TABLE SAB_XML_PERSIST
(
SESSION_ID VARCHAR2(36) NOT NULL,
CREATION_DT DATE DEFAULT sysdate,
UPDATE_DT DATE DEFAULT sysdate,
XML_TYPE VARCHAR2(12) NOT NULL,
XML_STREAM CLOB
)
LOB (XML_STREAM) STORE AS SAB_LOB_DATA_XML_PERSIST
( TABLESPACE EDECLIC_DATA
STORAGE (MAXEXTENTS 8192)
INDEX SAB_LOB_INDEX_XML_PERSIST
( TABLESPACE edeclic_index
STORAGE ( maxextents 8192 )
)
)
TABLESPACE EDECLIC_DATA
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_%'

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS TABLESPACE_NAME
------------------------- -------------- ----------- ----------- -------------------------
SAB_XML_PERSIST 131072 131072 2147483645 EDECLIC_DATA
SAB_LOB_INDEX_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 ?

Thanks

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)
  4  TABLESPACE "T1_DATA"
  5  LOB ("Y") STORE AS (TABLESPACE "T2_DATA" )
  6  /
 
Table created.
 
ops$tkyte@ORA9IR2> select segment_name, tablespace_name from user_segments;
 
SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
T                              T1_DATA
SYS_IL0000034510C00002$$       T2_DATA
SYS_LOB0000034510C00002$$      T2_DATA


the syntax you are using is 

a) deprecated
b) ignored

by design. 

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

SQL> desc TEMP.TT_ATTRIBUTE_VALUES
 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.

Pradikan


  

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

Pradikan

Tom,

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?

Thanks,

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?

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?

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?

Great. Now I understand. Thanks for succinct explanation.

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

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 ?

Thanks 
 

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 .

Thanks

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

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';

TABLE_NAME        COLUMN_NAME          INDEX_NAME
-------------------------    --------------------  ------------------------------
T                 VALUE                SYS_IL0000061891C00004$$

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

  COUNT(*)
----------
     10282

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

  COUNT(*)
----------
        22

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

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
         65536    10485760

SQL> 

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.

Manoj 

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.

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?

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.

Regards,
Sachin

You can rename a lob segment name as follows:
ALTER TABLE table_name MOVE LOB (column_name) STORE AS segment_name (TABLESPACE tablepace_name);

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?

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

Table created.

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

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
SEGMENT_NAME
------------------------------
T
X
SYS_LOB0000127812C00001$$

ops$tkyte%ORA11GR2> ALTER TABLE t MODIFY LOB (x) (SHRINK SPACE);

Table altered.

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.

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
nocache
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.