Oracle Data Pump – Export Import -4

Hi,

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

 

Read the following articles before this article.

 

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 oracle@192.168.63.63:/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



  

Mehmet Salih Deveci

I am Founder of 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 mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

3 thoughts on “Oracle Data Pump – Export Import -4

  • August 4, 2019 at 7:53 pm
    Permalink

    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.

    Reply
    • August 5, 2019 at 7:29 am
      Permalink

      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.

      Reply

Leave a Reply

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