How to import a schema to a new server in a different tablespace : Oracle 10g

1.  Copy the backup of the dumpfile to the target server.

2.  Create a tablespace in our eg: called TEST. Make sure this tablespace is atleast 20% larger than the size of your dumpfile(its not a hard n fast rule but its better IMO).

3. Create a user TEST and grant unlimited quota on the tablespace TEST to it. Make sure you assign the role imp_database_full and exp_database_full to it. Grant create session role also the user TEST.

4. Now create the directory from sql*plus like below

create or replace directory import_dir as ‘/datafiles’;

grant read,write on directory import_dir to public;

5. Now import the dump using impdp…remember if you used expdp to export import with impdp

impdp test/test123@swx directory=import_dir dumpfile=exp_swx_26052010.dmp logfile=import_test.txt remap_schema=swx:test remap_tablespace=users:test parallel=2

few points to be noted:

1. The dumpfile was created for schema name called SWX

2. The original tablespace was called users, the new tablespace is called test

3. ‘parallel’ specifies the no. of parallel execution processes.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s