Steps to import a database dump

Here we will discuss import a database with different schema’s:

Using an export file (.dmp) creating from any SCHEMA,TABLESPACE combination to another SCHEMA,TABLESPACE combination

Prerequisities:

* A valid export file (*.dmp) and knowledge on the SCHEMA and TABLESPACE it was exported from. We will call these as SRC_USER, SRC_TBLSPC_DATA and SRC_TBLSPC_IDX henceforth.
* Creation of the schema and tablespace on the destination database. We will call these DEST_USER, DEST_TBLSPC_DATA and DEST_TBLSPC_IDX henceforth.

Steps:

* Open the command prompt and navigate to the folder where Dump file resides (Say C:):
* Run the following command on the Destination Database

	imp DEST_USER/DEST_PASS@DEST_DB file=exportfile.dmp indexfile=INDEX_FILE.sql fromuser=SRC_USER touser=DEST_USER;

where DEST_DB is the SID of the destination database and INDEX_FILE.sql is the file that will be created in C: after running the above command (You can use any name in the place of INDEX_FILE.sql)

* Open the INDEX_FILE.sql created with a suitable text editor (an editor with regular expression based search/replace would be helpful Ex: EditPlus3)
* Replace the “REM ” (REM with two spaces) with “” (Empty, Nothing). Use the “match whole word” feature of your editor. Else, results might be disastrous
* Replace SRC_USER with DEST_USER
* If you find DEST_USER Schema with the statements < “DEST_USER SCHEMA;”>, then remove the ; after the DEST_USER SCHEMA
* Replace SRC_TBLSPC_DATA with DEST_TBLSPC_DATA
* Replace SRC_TBLSPC_IDX with DEST_TBLSPC_IDX
* If using an editor with regular expression facility, replace “… [0-9]* rows” (Starts with …, then a number, then the word rows) with “” (Empty, Nothing)
* If you find CONNECT statements in the file, remove the entire line
* Save and close INDEX_FILE.sql
* Login to the database using sqlplus, as DEST_USER

	sqlplus DEST_USER/DEST_PASS@DEST_DB

* Run the INDEX_FILE.sql file
* This will create all the objects in the newuser schema

	SQL> @INDEX_FILE.sql;

* Exit from sql prompt
* Login to the database using sqlplus, as DEST_USER

	sqlplus DEST_USER/DEST_PASS@DEST_DB

* Disable all constrain using below query

	 SQL> spool on;
	 SQL> spool disable_con.sql;
	 SQL> select 'ALTER TABLE '||substr(c.table_name,1,35)|| ' DISABLE CONSTRAINT '||constraint_name||' ;/'from user_constraints c, user_tables u where c.table_name = u.table_name;
	 SQL> spool off;

* Exit from sql prompt
* Open the disable_con.sql file with a suitable text editor
* Remove the following lines if it is present. Usually it will be available at the top of the sql file. The SQL file should only contain the ALTER TABLE statements.

spool on;
spool disable_con.sql;

* Save and close disable_con.sql
* Login to the database using sqlplus, as DEST_USER

sqlplus DEST_USER/DEST_PASS@DEST_DB

* Run the disable_con.sql file

SQL> @disable_con.sql;

* This will disable all the constrains
* Exit from sql prompt
* Run the following command before running import command

	In Windows:
	set NLS_LANG=_.AL32UTF8

	In UNIX (Solaris):
	NLS_LANG=_.AL32UTF8
	export NLS_LANG 

* Run the following command on the Destination Database (note the variation in parameters)

imp DEST_USER/DEST_PASS@DEST_DB file=exportfile.dmp ful=Y ignore=Y log=logfile.log

* Wait for the import to complete.
* Enable all constrain using below query

	 SQL> spool on;
	 SQL> spool enable_con.sql;
	 SQL> select 'ALTER TABLE '||substr(c.table_name,1,35)|| ' ENABLE CONSTRAINT '||constraint_name||' ;/'from user_constraints c, user_tables u where c.table_name = u.table_name;
	 SQL> spool off;

* Exit from sql prompt
* Open the enable_con.sql file with a suitable text editor
* Remove the following lines if it is present. Usually it will be available at the top of the sql file. The SQL file should only contain the ALTER TABLE statements.

spool on;
spool enable_con.sql;

* Save and close enable_con.sql
* Login to the database using sqlplus, as DEST_USER

sqlplus DEST_USER/DEST_PASS@DEST_DB

* Run the enable_con.sql file

SQL> @enable_con.sql;

* This will enable all the constrains
* Exit from sql prompt
* Check for the Invalid Objects

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.