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

Thread: Regain space

  1. #1
    Join Date
    May 2002
    Posts
    46

    Regain space

    Hi,
    We have a large table with 45 millions records and the table has an LOB column. The table occupies 180GB of space including the LOB segments. We will not be able to delete the records in the table but the LOB is not required after a given period of time.

    Will we be able to gain some disk space if we set the LOB to NULL for 10% percent records. The PCTFREE and PCTUSED defined for the table are PCTUSED 40 and PCTFREE 10.

    S.Prabhakar

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134
    Uhm.........What is the question?
    I remember when this place was cool.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: Regain space

    Originally posted by sprabhakar
    Will we be able to gain some disk space if we set the LOB to NULL for 10% percent records.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you use in-line storage for LOB col?

    Tamil

  5. #5
    Join Date
    May 2002
    Posts
    46
    Hi,
    Yes I am using Storage in row.

    S.Prabhakar

  6. #6
    Join Date
    May 2002
    Posts
    46
    Any updates?

    S.Prabhakar

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by sprabhakar
    Any updates?

    S.Prabhakar
    Well, set the LOBs to NULLs.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    For a 45 M rows that occupy 180GB space I would not opt for in-line storage of LOB. You could have very well opted for out-of-line storage. If a row has been chained, then Oracle may not put back the row into the original block even though the LOB data column shrinked b/c of an update.

    Do you see many chained rows in the table?

    May be you need one time re-org of the table after the update on the LOB column.

    Tamil

  9. #9
    Join Date
    May 2002
    Posts
    46
    Tamil,

    The table does not get updated at any point in time. There are no chained rows present in the table.

    We had opted for the inline storage of LOB's as the size is less than 4K.
    Are there specific advantages of using out-of-line storage ?
    Could you please advice of the apt method to do the table re org.

    S.Prabhakar

  10. #10
    Join Date
    Jul 2003
    Posts
    323
    Since you have no chained rows and table is static..

    Do u want to reclaim disk space ? OR
    Do you want to do a re-org ? AND
    Where did u get the " Magic ?" 10% value to set the LOB's to NULL?

    Do enlighten....


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