Oracle Database Tutorial How to Import Schema using REMAP SCHEMA in impdp Data Pump Import

YouTube video

In this Oracle Database tutorial, we will learn how to import a schema using the REMAP_SCHEMA feature in impdp (Data Pump Import). Importing a schema is a crucial task when transferring data from one database to another, and the REMAP_SCHEMA feature allows us to remap the schema name while importing.

Introduction

When migrating databases or transferring data, it is common to encounter the need to change the schema names. Oracle provides the REMAP_SCHEMA feature in impdp to facilitate this process. By using REMAP_SCHEMA, we can change the schema name during the import process, eliminating the need for manual schema name changes after the import.

Step by Step Process

To import a schema using REMAP_SCHEMA, follow these steps:

  1. Preparation: Make sure you have the necessary privileges to perform the import operation. Also, ensure that you have exported the schema to be imported using expdp (Data Pump Export).

  2. Connect to the target database: Open your Oracle command-line interface or SQL Developer and connect to the target database where you want to import the schema.

  3. Move the export dump file: Transfer the export dump file (generated by expdp) to the target database server. Make sure the dump file is accessible to the impdp utility.

  4. Execute the impdp command: Now, it’s time to execute the impdp command with the appropriate parameters.

markdown
impdp username/password@database_name \
remap_schema=old_schema:new_schema \
directory=directory_name \
dumpfile=dumpfile_name \
logfile=logfile_name.log

Replace username/password with your actual login credentials, old_schema with the original schema name, new_schema with the desired schema name, directory with the directory object name where the dump file is located, dumpfile with the name of the export dump file, and logfile with the desired name for the log file.

  1. Monitor the import process: Once the impdp command is executed, you can monitor the import process by checking the log file specified in the command. This log file will provide details about the progress and any errors that may occur during the import.

  2. Verify the imported schema: After the import process is complete, verify the imported schema by querying the target database. Make sure all the tables, data, and other objects are imported correctly with the new schema name.

Key Points

Here are the key points to remember when using REMAP_SCHEMA in impdp:

  • REMAP_SCHEMA allows you to change the schema name during the import process.
  • This feature saves time and effort by eliminating the need for manual schema name changes after the import.
  • Make sure you have the necessary privileges to perform the import operation.
  • Prior to importing, export the schema to be imported using expdp.
  • Move the export dump file to the target database server and ensure its accessibility.
  • Use the impdp command with the appropriate parameters, including remap_schema to specify the old and new schema names.
  • Monitor the import process by checking the specified log file.
  • After the import process, verify the imported schema to ensure its correctness.

By following these steps and utilizing the REMAP_SCHEMA feature in impdp, you can easily import a schema into an Oracle database with a new schema name.

In conclusion, importing a schema using REMAP_SCHEMA in impdp (Data Pump Import) is a convenient way to change the schema name during the import process. It saves time and effort by eliminating the need for manual schema name changes after the import. By following the step-by-step process outlined in this tutorial, you can successfully import a schema with a new name into your Oracle database.

Remember to always double-check the imported schema and ensure its correctness to avoid any data integrity issues.