Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4

Hi,

In this article I will continue to explain the data pump technology in Oracle databases.

Read the following articles before this article.

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-1

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-2

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-3

Import can be done directly over the network link without export. In other words, a database link is created between production and test database and it can be used for import directly into the test database without exporting from production. To do this, you need to have a dblink from the test database to the production database. If no dblink is available, you can create it with the following command.

CREATE PUBLIC DATABASE LINK DB_LINK_NAME
CONNECT TO username
IDENTIFIED BY password
USING 'PRODUCTION_IP:PORT/SID';

We can import the HR schema directly into the test database without exporting like following.

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=replace NETWORK_LINK=DB_LINK_NAME

Using the database link, the direct import process works a lot of time, especially when the table or the schema size is too large, we can handle this process over the network without any extra cost.

You can export spesific schema and import it into new schema name. Or you want to change schema name when it uses in test or development database.

You can export MEHMET schema with compressed like following.

expdp \”/ as sysdba\” schemas=MEHMET directory=DATAPUMP dumpfile=MEHMET.dmp logfile=MEHMET_export.log compression=all

Transfer it into test database.

[oracle@MehmetSalih ~]$  scp /backup/MEHMET.dmp [email protected]:/Backup

You can import MEHMET schema as SALIH using REMAP_SCHEMA option like following. MEHMET schema is named as SALIH in the test database.

impdp \"/ as sysdba\" schemas=MEHMET directory=DATAPUMP dumpfile=MEHMET.dmp logfile=MEHMET_export.log remap_schema=MEHMET:SALIH

This remap schema option is used for creating test or development database from production.

You can change table name while it is moving with export import. For example: my DEVECI table which is in the MEHMET schema is exported and I want to change its name with SALIH name.

You should export it like following with compressed.

expdp \"/ as sysdba\" TABLES=MEHMET.DEVECI directory=DATAPUMP dumpfile=mehmet_deveci.dmp logfile=mehmet_deveci.log compression=all

İmport it with SALIH name using REMAP_TABLE option. MEHMET.DEVECI table will be MEHMET.SALIH name after import.

impdp \"/ as sysdba\" TABLES=MEHMET.DEVECI directory=DATAPUMP dumpfile=mehmet_deveci.dmp logfile=mehmet_deveci.log REMAP_TABLE=MEHMET.DEVECI:SALIH

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/