Re: exp/imp and extents

richie wrote:
>
> Running Oracle 8i and 9i on Red Had advanced server. I came across
> something today and I'm unsure if i'm just imagining things but....
>
> I was exporting a few tables from one of our 8i databases. One table
> had about 943159 rows and was 231964672 bytes the extent size was 1M
> so around 200+ extents where being used for this table. I exported it
> with rows=n because I just wanted the table structure. I then tried
> to import it into another one of our 8i databases which had about a
> gig and half of available space in the tablespace. It wouldn't
> impor...said it couldn't create the initial extent. The initial
> extent size for this table was 1M as well. I then assumed it was
> actually trying to create the object and allocate the 200+ extents for
> the object. I added another 2G datafile. I then was able to import
> the object.
>
> From the dba_segments the new empty table in the new database had
> 231964672 bytes allocated and 1 extent the initial extent size was
> 231964672 bytes. There where 0 rows in the table. The default
> extent size for the tablespace importing into is 1M the pctincrease is
> 0 all around.
>
> I would of expected the new object to take up 1M since it had no data
> in the table. However, it allocated just as much space from the
> tablespace as if it had all the data in the table.
>
> Is this right? Should this be right? If so, why why why?
>
> Any comments would be greatly appreciated.
> thanks.
> -richie

When you export, there is an option called COMPRESS, which defaults to Y. This means that all of your extents will compress into one extent. In your case, INITIAL would be set to 200M. When you allocate an extent, the space would all have to be contiguous for the extent to be allocated. A 200M extent can be hard to find space for it. When you added more space, it was all contiguous and thus a 200M extent could be allocated. This process does not work this way if you are using a LMTs space is not allocated this way.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"