Re: exp/imp and extents

Welcome to the world of Oracle's bizarre defaults.

You happen to have encountered what is quite possibly the worst of them: COMPRESS=Y as part of export.

It means that on subsequent import, a table will be created with an initial extent equal in size to all the existing extents in the source database.

It was invented because some idiot back in version 5 or thereabouts decided that it would be good for performance to have all segments comprised of single extents. It was a daft theory then, and it's even dafter now... but the default value has never changed in all that time.

Regards
HJR "richie" <richchri_at_erols.com> wrote in message news:9a436674.0403221145.85ec3ea_at_posting.google.com...
> 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
Received on Mon Mar 22 2004 - 17:16:53 CST