Oracle Database Discussions

Hi all!

I'm planning to migrate some schemas from a 10.2.0.4 database to an 11.2 database using datapump.
At the same time i want to store the data in compressed format (compress for oltp - using advanced compression).
What is the best way to achive this?

I have tried a couple of different ways...

1) Pre-create all tablespaces with compress.
and then import with transform=segment_attribute:n:table

Then all tables are imported with compression, but all objects ends up in the default tablespace for that schema, since the attribute removes the "tablespace" clause from the export file. This is not feasible since i have many partitions in different tablespaces.

2) Pre-create all tablespaces with compress.
and then import with transform=storage:n:table

Then the tablespace clause is included but so is the NOCOMPRESS clause... so all tables are uncompressed after import.

3) Pre-create all tablespaces with compress.
Create a sqlfile with content=metadata_only and then edit the sqlfile replacing NOCOMPRESS with COMPRESS FOR OLTP.

Then do an import with content=data_only

This will give me the result i want, but is quite time consumuing..

Is there another way i can do this, by just pre-creating tablespaces with compression and do regular import?


Thank you very much in advance!