CLOB column size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: CLOB column size

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hi,

    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

    Thanks

    Sameer


  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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...

    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?

    Thanks

    Sameer

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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?

    Sameer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width