Re: Oracle 9i datafile sizes compared with 8i

"Austin" <austint_at_vsnl.com> wrote in message news:<I06oqA.GsB_at_news.boeing.com>...
> Hi DBAs,
>
> I am facing some problems importing an 8i export file into 9i instance.
>
> I have created tablespaces with same sizes in 9i before the import (used the
> "create tablespace.." command from the export dmp file). I find that the
> tablespaces are getting full prematurely into the import. I would expect the
> 9i tablespace to hold the same amount of tables/records as the 8i tablespace
> of the same size. But after I set an initial extent to about twice the size
> of the 8i tablespace and retry, the import works fine.
>
> My question is - Is there a difference in the way 9i datafiles are managed
> compared to 8i datafiles, or have I missed setting some parameter on the 9i
> instance?

Sybrand's answer is the most likely, but it is also possible you simply have wrong pct[free|used] parameters in the tables or tablespaces. It is possible that a lot of update activity in the 8i has enlarged rows, filling up data blocks, then when you do the import there needs to be room for that expanded data plus free space for future updates (which have already been done in the past). Using the large initial extent might hide this because you aren't running into Oracle trying to extend segments in too small of a tablespace.

btw, use LMT's if you are not. (They default in 9, but we don't know what you've done.)

jg

--
@home.com is bogus.
http://www.nasa.gov/externalflash/cassini/indexmain.html