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

Hi,

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

Read the following first article before this article.

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

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 [email protected]:/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.

Next articles’ link are as follows.

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

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

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

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