How to Reclaim space by LOBSEGMENT in Oracle 9i

[Former Member]

Hello Gurus,

We have a sitaution where we have

SAP : ECC 6.0 (64-bit)

DB : Oracle 9i

OS: AIX

We downloaded the stats from DB02 > Segment Overview>Top Growth

and fond that there was a segment named below

SAPSR3 SYS_LOB0000076253C00006$$ LOBSEGMENT PSAPSR3 215520MB

The space occupied is 215GB , which is too large.

I am unaware how can we "RECLAIM" this space.

Also surprising thing was DB growth was increasing at a very high pace from Oct 2010 to Feb 2011 i.e about 40GB/month

Now suddenly from March to May , we see a sudden drop of DB growth of only 3-4 GB/month.

This month itself we saw this "segment" LOBSEGMENT which consumed 215GB.

Will you please provide us more details on this segment and some tips on why it takes so much space and is there any way to recover the "space" occupied by LOBSEGMENT.

During our earlier analysis , we had also checked and found that NO Log Trace were enabled , nor the FI special Ledger consumed any much space.

There was a roll-out of a new plant , but its capacity was very small to other 3 plants( 10% of largest plant) , hence the question of New plants roll out also does not answer our query.

Requesting you to provie us your insight.

Thank You.

Regards,

Victor

[stefan_koehler]

Hello Victor,

well at first we need to know which table column is using this LOB segement. Please execute the following query.

SQL> select TABLE_NAME, COLUMN_NAME 
from ALL_LOBS where SEGMENT_NAME = 'SYS_LOB0000076253C00006$$';

"Reclaiming" of LOB space is only possible if the corresponding data is deleted. LOB segments are treated in some special way - at which point the "deleted" space can be reused or not.

For more information about the two options please check the documentation (PCTVERSION or RETENTION):

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2128956

Regards

Stefan