import tablespace into different database

[Raaj A]

Hi Tim,

I want to export a tablespace from one database and import into different database of same version and platform using exp/imp utility.
I am using 10g(10.2.0.1.0) on win2k3. Please help.


Thanks
Raaj

[Tim...]

Hi.

I've moved your post to the DBA forum.

Regarding your question, this is exactly what transportable tablespaces are for.

http://www.oracle-base.com/articles/mis ... spaces.php

Cheers

Tim...

[Raaj A]

Hi Tim,
Thanks for your suggestion.I followed your link and successfully imported tablespace into different database. I have one doubt. i did following steps.
1) ALTER TABLESPACE test_data READ ONLY;
2)$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log
3) copied datafile and dump file to target destination
4)ALTER TABLESPACE test_data READ WRITE;

on destination db side

1) CREATE USER test_user IDENTIFIED BY test_user;
2) imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data
3)ALTER TABLESPACE test_data READ WRITE;

My doubt is if there are 5 users(schema) which belongs to a single tablespace ( for e.g users tabelspace) on source db side then while doing tablespace import i need to create those 5 users(schema) on target db side? my doubt is becoz while doing above activity if the users is not present on target db side, during import it throws error.

thanks

[Tim...]

Hi.

Yes. You will need to create all the users who have objects in that tablespace or the import will have errors.

Cheers

Tim...