I have a table with only one column which is of type CLOB.
When I insert more than 4000 bytes, avg_row_len column shows me 44, if I insert around 8000 bytes the it shows me 48 ...
If rowsize is below 3964 bytes, then avg_row_len columns shows me whatever is the size of row.. But once it gets more than 3964 byes... the 44 and 48 business starts.. if bytes in row are 4000 or 8000 resp..
Please explain how these 44 and 48 are calculated?
I have just inserted only one record in this table.. I just wanted to know how row size calculation take place for CLOB datatype column
If the length of a CLOB is less than 4000 bytes it can be stored inline with the row. If it's greater than 4000 bytes it's stored in an overflow segment. The row contains just a reference to the overflow segment. This is why your rowsize drops suddenly once the rowsize exceeds a certain point.
Thanks for the reply Tim..
Is it possible to to findout the information about the overflow segment?
Does Oracle stores the overflow segment in the same tablespace? Because O8i docs says...
How can we findout the information about the overflow segment?
Once the LOB grows bigger than approximately 4K Oracle8i moves the LOB out of the table into a different segment and possibly even into a different tablespace.
Hence, Oracle8i sometimes stores LOB data, not just LOB locators, in-line in the row.
If you don't specify the tablespace for lob segment then oracle will create it in the same tablespace where the table is created.
You can find the tablespace of the lob segment by querying USER/DBA_LOBS and USER/DBA_SEGMENTS;
SELECT t.table_name, t.column_name, s.tablespace_name AS lob_tablespace
FROM USER_SEGMENTS s, USER_LOBS l, USER_TAB_COLUMNS t
WHERE s.segment_name = l.segment_name
AND l.table_name = t.table_name
AND l.column_name = t.column_name
AND t.table_name = 'MY_TABLE'
AND t.column_name = 'MY_CLOB_COLUMN';
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Thanks a lot....
I have one more query... I inserted two records...
SELECT sys.dbms_lob.getlength(text) FROM system.sameer;
gives me 5011 and 5011 bytes for first and second record resp..
When I inserted first record, BYTES in dba_segment where 32768 and when I inserted second record with same size, again it incremented by 32768 bytes. One more extent for allocated to LOB segment.
My question is when both records can fit into one extent easily (5011+5011 = 10022 which is < 32768 ) how come second extent got allocated to lob segment?
Click Here to Expand Forum to Full Width