Friends..
DB: 11gR2
OS: Linux
I'm trying to shrink lob segment and reclaim free space from the lob segments.
This area in Oracle DB is new to me so trying to learn and shrink segment.
I have gone through various metalink doco (386341.1) but not able to get answers for below...
Questions:
1. How much free space is available in the lob segment?
2. Can I assume "non LOB DATA" as the current free space and same will be recovered once segment will be shrinked?
3. Any disadvantage of using "ALTER TABLE .... SHRINK SPACE"
4. Is it true if segment storage is 100GB same amount of redo will be generated while trying to alter table... shrink space?
I'm following below steps;
1. Find Segment name -> sys_lob123
2. Find storage size -> 100 GB
3. Find size of the LOB data -> 70
4. Find size of the non LOB data -> 30
2. Storage size
select bytes "storage" from dba_extents
where segment_name = "sys_lob123"
100 GB
3. Size of LOB
select dbms_lob.getlength(col_lob) from table_lob;
70 GB
4. Size of non LOB data
100-70 = 30GB (i.e #3 - #2)
Thanks in advance and appreciate your time in looking into this...