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
remap_schema=source:target

2. The original tablespace was called users, the new tablespace is called test
remap_tablespace=source:target

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s