How to import and export Oracle user between servers¶
Server 1: Is the server where we will take the Oracle user dump file from.
Server 2: Is the server we need to copy the user to.
We need to copy the schema/user
YRP from the server
Server 1 to the server
and this user has two tablespace associated,
RWJEJU (we need to fix it).
2. Download dump file from
Server 1 to
2.1 Open a terminal on Server 2
2.2 Go to the dbdump folder
2.3 Check the name of the dump to download from
2.4 Download the dump with scp from Server 2
3. Extract the dmp file from the archive file¶
Extract the file inside the archive using the command
4. Check tablespaces associated with the user in order to fix them later¶
If more than one tablespace is present, these tablespaces have to be remapped during the import procedure (see below).
5. Create new user and tablespace on
oracle user and login with sqlplus
5.2 Check the folder that contains tablespace files in order to create the new tablespace in the correct folder
5.3 Create tablespace and user to
5.4 Check if it is possible to connect to the user from remote (sql developer)
6. Import the dump file to
6.1 Check the dump folder before executing the command
6.2 Start the import procedure
Execute the following command from the terminal
We have to remap two tablespace:
and a schema:
$ impdp system/[PWD] \ > remap_tablespace=YRP:YSG,RWJEJU:YSG \ > remap_schema=YRP:YSG \ > directory=DATA_PUMP_DIR dumpfile=[dmp file here]