Oracle: Steps to Install Dumps on Local Database

All Users have the oracleXE in our local machine, sometime we need to import the dump for Projects in local machine.

We need to follow these steps to import the dump in our local machine :

Step 1: Connect with the system user from cmd prompt.

sqlplus user/password@XE as sysdba;

Step 2 (optional):

For some Project we require TableSpace with the name “DATASPACES”, so we have to create the TableSpace first,

CREATE TABLESPACE DATASPACES DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\DATASPACES.DBF'
SIZE 4096M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

Note: DATASPACES.DBF file must be created inside the oradata\XE folder (XE is the default SID)

Step 3: Now create the user as you want but be specific as per the Project version.

  • create user username identified by password default tablespace users temporary tablespace temp quota unlimited on users;
  • grant dba to username;
  • commit;
  • exit;

Step 4: Import the dump.
               Execute the below code after commit and exiting the sql in the command prompt

 imp userid ='username/password@XE' file='C:\Latest_Dump\Project1\exp_USRTESTPRD_at_project_20150410.dmp' log='C:\Latest_Dump\OracleImportLogs\exp_USRTESTPRD_at_project_20150410.log' rows=y full=y

Note:

  • Make sure the dump is located in the location ‘C:\Latest_Dump\Project1
  • Might be sometime while you importing the dump the Tablespace name may be different so we need to open the dump file in the text editor and replace the tablespace name with the DATASPACES which be created.
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

%d bloggers like this: