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?
that is pretty much option 3 you are suggesting, no?
i'm creating a sqlfile with metadata only, but i have to remove the uncompress keyword from that file since table properties overrides the tablespace properties.
I was looking for an easier way, but maybe i just have to do it that way.