This forum is currently locked. You can't register or post questions at this time. (read more)

import tablespace into different database

All posts relating to Oracle database administration.

Moderator: Tim...

Raaj A
Senior Member
Posts: 51
Joined: Wed Mar 07, 2012 7:08 am

import tablespace into different database

Postby Raaj A » Mon Oct 22, 2012 11:39 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: import tablespace into different database

Postby Tim... » Mon Oct 22, 2012 12:55 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Raaj A
Senior Member
Posts: 51
Joined: Wed Mar 07, 2012 7:08 am

Re: import tablespace into different database

Postby Raaj A » Tue Oct 23, 2012 1:25 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: import tablespace into different database

Postby Tim... » Tue Oct 23, 2012 2:32 pm

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 5 guests

cron