1 Reply Latest reply: Jun 27, 2012 2:05 PM by dba-india RSS

    How to shrink lobsegment

    udayjampani
      Hi all,
      Greetings of the day,

      I have a table named t_stage which is having a blob column , stores the physical files,despite i have deleted the unwanted physical files, but the space is not getting reclaimed.

      Tried the below ,


      SQL> alter table t_stage modify lob(filedata) (shrink space);
      alter table t_stage modify lob(filedata) (shrink space)
      *
      ERROR at line 1:
      ORA-10635: Invalid segment or tablespace type


      SQL> alter table t_stage modify lob(LOB_SFS$_FST_1) (shrink space);
      alter table t_stage modify lob(LOB_SFS$_FST_1) (shrink space)
      *
      ERROR at line 1:
      ORA-00904: "LOB_SFS$_FST_1": invalid identifier








      SQL> select sum(length(filedata))/1024/1024/1024 from dbfs_user.t_stage;

      SUM(LENGTH(FILEDATA))/1024/1024/1024
      ------------------------------------
      525.705434

      SQL> select bytes/1024/1024/1024 from user_segments where SEGMENT_NAME='LOB_SFS$_FST_1';

      BYTES/1024/1024/1024
      --------------------
      1007.97278


      Please suggest me , how to reclaim space , which stops me to rebalance the diskgroup


      actual usage is 525 GB , but it is using 1007 GB of space in the segments.

      More info : the segment is securefile blob

      Edited by: udayjampani on Jun 20, 2012 4:47 PM
        • 1. Re: How to shrink lobsegment
          dba-india
          Is your database version higher than 10gR1...

          With Oracle 10g R1, when shrinking a table, the CASCADE option DOES NOT shrink LOB segments for that table.
          With Oracle 10g R2 and higher, when shrinking a table, the CASCADE option DOES shrink all LOB segments (and indexes) for that table.

          http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_85.shtml