Oracle Community Directory
Oracle Community FAQ
My Oracle Support Community (MOSC)
Go Directly To
Oracle Technology Network Community
My Oracle Support Community
OPN Cloud Connection
Oracle Employee Community
Oracle User Group Community
OTN Speaker Bureau
Automatic Storage Management
Please enter a title.
You can not post a blank message. Please type your message and try again.
This discussion is archived
: Jun 27, 2012 2:05 PM by
How to shrink lobsegment
Jun 20, 2012 6:17 AM
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;
SQL> select bytes/1024/1024/1024 from user_segments where SEGMENT_NAME='LOB_SFS$_FST_1';
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
This content has been marked as final.
Show 1 reply
Re: How to shrink lobsegment
Jun 27, 2012 2:05 PM
in response to
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.