2 Replies Latest reply on Dec 22, 2011 9:12 PM by Satishbabu Gunukula

    How to reclaim the space used for LOBSEGMENT, which is growing fast


      DB version
      OS RHEL v4
      Envi RAC on ASM

      On the production, in one of the schemas (PR_AA) on the tablespace PR_AA, i have

      1) a table (Table1) which has 3 columns of CLOB datatype. Current size of this table is 65 GB.
      2) Two LOBSEGMENTS 85GB + 25GB =110GB
      3) about 10 Indexes of Table1 are of 15 to 17 GB

      Table1 is being purged on a daily basis, but still the tablespace is growing like anything ( now the over all size of the tablespace is itself 190GB).

      What i need is,

      Q1 . Ways to reclaim the purged space from the table1.

      Q2. How to shrink the table/ tablespace? Is it possible to do it online without impacting the users/business?

      Q3. How to shrink/maintain LOBSEGMENTS space, that is , how to control the size or reclaim the space from LOBSEGMENT after the daily purge on the Table1?

      Quick help is much appreciated and thank you in advance.