Oracle Data Pump – Export Import -2

Hi,

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

data pump

 

Read the following first article before this article.

 

Schema and table based Export and import are done with the following command. For example, the HR schema used in the Production database or the person table under this scheme is needed for test or development purpose in the test database. We can move the related schema and tables with the following commands. First I’ll show the moving HR scheme like following.

After set the database profile in the Linux operating system, the following command is executed to export of HR Schema.

 

[oracle@MehmetSalih ~]$  expdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP DUMPFILE=HR.dmp LOGFILE=HR.log

expdp command is run with sysdba.

 

SCHEMAS: To be backed up schema or to be imported schema is specified with this parameter.

DIRECTORY: The logical equivalent of the path to keep this physical dump file on the operating system is specified by the DIRECTORY parameter. The file or files that are created during the export process will store to the path where this directory is located. Dumpfile is created under this directory path.

DUMPFILE: The physical dump file created by the export or imported dump file is specified in the DUMPFILE parameter with its name like HR.dmp.

LOGFILE: Logs produced during this process will be written to the log file specified by parameter LOGFILE

 

We can transfer this export file to the test server with scp command as follows.

 

[oracle@MehmetSalih ~]$  scp /backup/HR.dmp oracle@192.168.63.63:/Backup

 

This export file transfered to the test server is imported into the related test database as follows.

[oracle@Deveci ~]$  impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP DUMPFILE=HR.dmp LOGFILE=HR.log table_exists_action=replace

 

When we examine the above impdp command, this process is done with sysdba as in expdp. The other parameters ( DIRECTORY,DUMPFILE,SCHEMA,LOGFILE ) are the same as expdp above, so the table_exists_action parameter is different.

 

Table_exists_action: While Import process is running if any table exists in database what will Oracle do ? replace existing table or skip if it exists. this parameter can take following values.

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

SKIP is default value. If SKIP is used then table replacing is not done.

APPEND  loads rows from the export files and leaves target existing rows unchanged.

TRUNCATE deletes existing rows in target table and then loads rows from the export.

REPLACE drops the existing table in the target and then creates and loads it from the export.

 

Mehmet Salih Deveci

I am 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.

Leave a Reply

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