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.
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...
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.
How can we findout the information about the overflow segment?
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';
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?