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.

Category: Database


Leave a Reply

Article by: Shadab Mohammad