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;
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
- 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.