Steps to import a database dump

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

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

* 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

* 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