Thursday, 9 February 2012

How to import tables to a different tablespace using original import utility

Oracle offers no parameter to specify a different tablespace to import data into while using original export and import utilities for taking logical backup. Objects will be re-created in the tablespace they were originally exported from. We can alter this behaviour as below.

Note:We can specify a different tablespace while using data pump using remap_tablespace option

    * Import the dump file using the INDEXFILE option

    imp username/password>@db_tns_name file=filename.dmp indexfile=index.sql full=y

    * Edit the indexfile. Remove remarks and specify the correct tablespaces.

    * Run this indexfile against your database, this will create the required tables in the appropriate tablespaces

sqlplus username/password@db_tns_name @index.sql

    * Import the table(s) with the IGNORE=Y option.

imp username/password@db_tns_name file=filename.dmp fromuser=from_user  touser=to_user ignore=y 

    Note: you can also import objects to different tablespace by
changing the default tablespace for the user to new tablespace where objects need to be imported and Revoke the user's quota from the tablespace from where the objects were exported. This will force the import utility to create tables in the user's default tablespace.