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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

11 comments

  1. Just commenting to register my agreement. Your post is well put together Nice job!

  2. Salih Bey (Hocam)

    expdp ile yedeğini aldığım(11.2.0.1) bir backup var.Bunu üst sürümlerde impdp ile sisteme alabilir miyim? (13g veya 18xe )
    Bir de ugrade işlemini patch işlemi gerekmeden yükseltme yaptığım sürümler var. Fakat bir de patch işlemi yaparak test etmek istiyorum. Örn 11.2.0.1(patch 13390677) sürümünde 12 ye geçtikten sonra daha üst sürümlere çıkartmak istediğimde patch indirmeye izin vermiyor ve yönetici izni istiyor Bu aşamada yardım veya yol göstermenizi rica ediyorum.

    • alt sürümlerden üst sürümlere aldığınız exportu import edebilirsiniz.
      Patch de izin vermiyor , yönetici izni istiyor dediğiniz şey metalink mi Oracle mı tam anlayamadım.

      11.2.0.1 den direk 12c ye upgrade yok. En az 11.2.0.2 yada üst versiyonlarından 12c ye upgrade edebilirsiniz.

  3. SOHAIL AHMED SYED

    Really Nice post. Implemented all the scenarios one by one. Thank You so much for explaining in a simple and very easy way.

  4. How do import data based on where condition on target system( on same table)

    Eg: I have 1 million records while doing export & while import I need to use where condition to insert half million records.Can you please explain how we can achieve this

Leave a Reply

Your email address will not be published. Required fields are marked *