4 Replies Latest reply: Mar 10, 2008 12:22 PM by 607104 RSS

    BLOB Column Query

    580867
      Hi,
      I have a CLOB column defined NOT NULL with data in it. Its a huge database of almost 2 TB and the management wants it to be reduced. If I need to SHRINK the contents of this column only on selected rows what are my options ? I will be given specific criteria and for those records I need to get rid of the BLOB content and shrink it. This is what the management desires so dont get me wrong if it sounds stupid. I am looking for suggestions.
        • 1. Re: BLOB Column Query
          SatishKandi
          After reading your post, a question that comes to my mind is, if the column is defined as NOT NULL, how do you propose to get rid of the data inside the column? You cannot set it to NULL; so presumably you will update it with smaller string like f.ex. "DUMMY".

          Is the data causing
          1. backup delays?
          2. performance problems?

          If it is related to backup, then you can evaluate partitioning.
          If it is related to performance, then you have other options along with partitioning.
          • 2. Re: BLOB Column Query
            580867
            Hi Satish,
            If I have to insert a DUMMY file which will be an UPDATE right ? I need to be able to reclaim the freed space back to the tablespace. Will that be possible ? I have a meeting and I am going to be the main course. Thank you for the suggestions.
            • 3. Re: BLOB Column Query
              SatishKandi
              Is it a BLOB, CLOB, NCLOB or BFILE ?

              Also, check out

              http://halisway.blogspot.com/2007/06/reclaiming-lob-space-in-oracle.html
              • 4. Re: BLOB Column Query
                607104
                If you alter the table, it invalidates the packages. Is there any alternative

                Thanks