Re: expdp dump file and CLOB column
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 13 Oct 2008 01:10:51 +0200
Message-ID: <48F283FB.8070103@gmail.com>
>
> 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.
>
>
>
>
Date: Mon, 13 Oct 2008 01:10:51 +0200
Message-ID: <48F283FB.8070103@gmail.com>
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? >> 10.2.0.4 >> >>> I did some very basic >>> tests on 10.2.0.4 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