DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Migrating Longs to CLOBS

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Migrating Longs to CLOBS

    I'm updating a database designed circa 7.3 and moving the LONGs and LONG RAWs into CLOBs and BLOBs.

    Is the 'best' practice to create a LOB specific Tablespace and store the LOBs in this. Is it a big performance hit to retrieve from the LOB doing this, if the records PK is in a different Tablespace?

    What are the disadvantages of leaving CLOBS as columns in the tablespace with the rest of the record?

    Also - the description of the LOBS in dba_segments is very ugly (system generated names) can this be changed at all (at table create time perhaps?).

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Sorry to have to bump this one back up to the top.
    Really interested in your comments on this LOB query.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi, were you "Jack" on Ask Tom? If not, here is Tom's answer to the naming bit:
    Code:
    In order to "name them", you do that in the create:
    
    ops$tkyte@ORA9IR2> create table WMS_CLOB (
      2      wmsObjectId    NUMBER(15,5),
      3      wmsObjectType    VARCHAR2(20),
      4      wmsClob    CLOB
      5  ) tablespace users
      6  storage ( INITIAL 256K NEXT 256K PCTINCREASE 0 )
      7  LOB (wmsClob) STORE AS my_Nice_Lob_Name
      8      ( TABLESPACE tools
      9        DISABLE STORAGE IN ROW
     10        STORAGE (INITIAL 32K    NEXT 32K )
     11        CHUNK 4
     12        INDEX my_nice_index_name (TABLESPACE manual
     13           STORAGE ( INITIAL 16K NEXT 16K )
     14          )
     15      )
     16  /
     
    Table created.
     
    ops$tkyte@ORA9IR2> select segment_name, segment_type, tablespace_name from 
    user_segments;
     
    SEGMENT_NAME                   SEGMENT_TYPE       TABLES
    ------------------------------ ------------------ ------
    WMS_CLOB                       TABLE              USERS
    MY_NICE_INDEX_NAME             LOBINDEX           TOOLS
    MY_NICE_LOB_NAME               LOBSEGMENT         TOOLS
    
    
    Now, note the index is in the same tablespace as the LOBSEGMENT.  That'll always 
    -- always be true now.  You cannot have them in two different places.
    
    Also -- please, please use LMTs!!  no more initial, no more next, no more 
    pctincrease, no more minextents, no more maxextents.  Consider them "legacy 
    things we do not bother with in 2004"

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If the LOB's are likely to be an order of magnitude bigger than the rest of the row - then you may want to have them in a TS with a bigger extent size?

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