HELP: Import generates too many space allocation

[Turin]

Hi friends,

I'm migrating my database from 8i to 9i.
I made a blank 9i database and I'm using the IMP/EXP utility to export the information from 8i to 9i.
The problem is that the segments of the schemas's are growing a lot (200%), so a schema that allocates 50Mb in 8i, now allocates almost 100Mb in 9i.
I created the control file of the 9i database with a different value of the parameter MAXINSTANCES. Is it the reason of the increment
of the space?

I'm importing the information in two phases, using the commands:

imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=y indexes=n constraints=n grants=n ignore=y log=import1.log

imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=n indexes=y constraints=y grants=y ignore=y log=import2.log


Any help will be very appreciated. Thanks a lot for your answers.

[gandolf989]

What is the minumum extent sizes on your tablespaces? A large minimum extent size will cause your database to grow.

[marist89]

Originally posted by Turin

I created the control file of the 9i database with a different value of the parameter MAXINSTANCES. Is it the reason of the increment
of the space?

No.

I'm importing the information in two phases, using the commands:

imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=y indexes=n constraints=n grants=n ignore=y log=import1.log

imp system/manager file=mydump.dmp buffer=30000 commit=y full=y rows=n indexes=y constraints=y grants=y ignore=y log=import2.log

Probably what's happening is a combination of differing minimum extent sizes and a "feature" of the export/import process. exp by default uses compress=y so that all your extents get compressed into one large extent. To bypass this "feature", use compress=n...

[Turin]

Thank you very much for your answers.

The block size of both source and target databases is the same (4096).

I'm using LMT in the source 9i database.

The size of the blank target tablespaces before importing the data are 200% bigger than the source tablespaces and I'm receiving the error:

IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace XXXXXX

I will test using the option compress=n and I will notify you if I solve the problem.

Thank you very much, again.

[Turin]

I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
I don't know what is happening, perhaps LMT is not a good option.
Does anybody have an idea how to solve this problem ???

Regards and thanks again.

[nagarjuna]

Originally posted by Turin
I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
I don't know what is happening, perhaps LMT is not a good option.
Does anybody have an idea how to solve this problem ???

Regards and thanks again.

What about your pctfree?? and pctused???

[nagarjuna]

Originally posted by gandolf989
What is the minumum extent sizes on your tablespaces? A large minimum extent size will cause your database to grow.

Table level storage clauses over write default tablespace level storage parameters.

[gandolf989]

Originally posted by Turin
I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
I don't know what is happening, perhaps LMT is not a good option.
Does anybody have an idea how to solve this problem ???
Regards and thanks again.

you might want to post the sql you used to create your tablespaces. You can also write dynamic sql that will create all of your tables without foriegn or primary keys, run the script on your target database and then do a full import with ignore=y. This will remove all of the sizing from all of your tables. You will still need to create the indexes. Which you can do that through dynamic sql as well.

[jmodic]

Originally posted by Turin
I try to use the option compress=N of the EXPORT utility with exactly the same results. A tablespace that allocates 50 Mb in 8i, now allocates 120 Mb in 9i.
I don't know what is happening, perhaps LMT is not a good option.
Does anybody have an idea how to solve this problem ???

Wee need more datails, but in short: LMT is good, the best and (in the near future) the only option.

What we need to explain what is happening is at least the following:
- UNIFORM or AUTOALLOCATE LMT?
- if UNIFORM, what is the extent size in that tablespace?
- what are the storage parameters for that LMT?

[Turin]

Here I send you more relevant information:


* The PCT_FREE value of all my tables in either source and target databases are the same.

I extracted this information with the query:

select table_name, pct_free, pct_used
from dba_tables
where owner='xxxx'
order by owner, table_name


The value of PCTFREE is not showed because I'm using automatic space management (I think that). The values that I got with the above query, in my Source Database, were: 40% and 30%.

* I created each user's tablespace in this way:

CREATE TABLESPACE "XXXX"
LOGGING
DATAFILE '/xxxfile.dbf' SIZE 50M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

I really expect to solve this problem, that doesn't let me sleep well.

Thank you all of you that share your knowledge with the rest of the world.

Regards!