Re: expdp dump file and CLOB column

From: Maxim Demenko <>
Date: Mon, 13 Oct 2008 01:10:51 +0200
Message-ID: <>

Mladen Gogala schrieb:
> On Sun, 12 Oct 2008 12:03:27 +0000, Mladen Gogala wrote:

>> On Sun, 12 Oct 2008 12:09:13 +0200, Maxim Demenko wrote:
>> On Sun, 12 Oct 2008 12:09:13 +0200, Maxim Demenko wrote:
>>> What is Oracle version you are speaking about?
>>> I did some very basic
>>> tests on and couldn't see anything similar to compression. I
>>> assumed, compression rate should be (doesn't matter what compression
>>> method is used) highest for most redundant data - in my testcase
>>> dumpfile size was the same for clobs filled either with dbms_random or
>>> with blanks. What size return sum(dbms_lob.getlength(lob_column)) ?
>>> Does it nearly correlate with 45Gb ?
>> No, it doesn't:
>>   1  select sum(dbms_lob.getlength(segment_text))/1048576 MB 2* from
>>   news_segments partition(data0544)
>> SQL> /
>>         MB
>> ----------
>> 8060.59686
>> It correlates more closely with 9GB.
>>> If not, i would assume a massive space overallocation ( for whatever
>>> reason) during import.
>> Yes, that's probably it. I will attempt the normal export, no "dp"
>> version, and see what happens. That's probably the dirty little secret
>> of data pump: waste humongous amounts of space. I am not surprised that
>> Oracle Corp. decided to venture into storage business.
>>>  Another points to
>>> consider could be of course the charactersets on both source and target
>>> database and the original size of lob segments in the source.
>> Both character sets are the same.

> I just tried with normal export, with the same result. It seems that
> oracle grossly overallocates LOB columns in 10g. In other words, LOB
> columns are enormous space wasters. In my case, it wasted +45GB of space
> to store just 8.5GB of data. Yeah, I understand now why Oracle Corp.
> went into storage business. LOB columns are their greatest booster.

I think, what you observe, should have rational explanation - though, it is difficult to make sensible assumptions without knowing details. Anyway, a note 386341.1 may give you some pointers if you like to investigate it further. Shrink should probably do a workaround if your nearest goal is the space deallocation, however one should be cautious - iirc, there were a plenty of bugs with shrink and lob corruptions in previous releases/patchsets.

Best regards

Maxim Received on Sun Oct 12 2008 - 18:10:51 CDT

Original text of this message