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

Hi,

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

 

Read the following first and second 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

 

 

Exports in Data pump technology can be compressed and encrypted in for security and compression. You can take compressed and encrypted export like following.

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

 

You can export of lots of Schemas  in a same process( MEHMET, SALIH, DEVECI )like following.

[oracle@MehmetSalih ~]$ expdp \"/ as sysdba\" SCHEMAS=MEHMET,SALIH,DEVECI DIRECTORY=DATAPUMP DUMPFILE=mehmet_salih_deveci.dmp LOGFILE=mehmet_salih_deveci.log compression=all encryption=all

 

In the following example, the personel table of the HR schema is exported and imported.

expdp \"/ as sysdba\" tables=HR.personel DIRECTORY=DATAPUMP DUMPFILE=personel.dmp LOGFILE=personel.log

 

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. Table will be appended with old and current values.

impdp \"/ as sysdba\" tables=HR.personel DIRECTORY=DATAPUMP DUMPFILE=personel.dmp LOGFILE=personel.log table_exists_action=append

 

You can export and import lots of tables in the same export process like following scripts.

expdp \"/ as sysdba\" tables=HR.personel,HR.sales DIRECTORY=DATAPUMP DUMPFILE=personel_sales.dmp LOGFILE=personel_sales.log

impdp \"/ as sysdba\" tables=HR.personel,HR.sales DIRECTORY=DATAPUMP DUMPFILE=personel_sales.dmp LOGFILE=personel_sales.log table_exists_action=replace

 

You can take full database export with datapump. But full database export is not like RMAN, In general terms, data pump does not provide for full backup like RMAN. RMAN is prefered for database full backup instead of datapump. Data pump is very effective for table and schema moving or backup.

You can take full database export with datapump like following.

expdp \"/ as sysdba\" DIRECTORY=DATAPUMP DUMPFILE=FULL_EXPORT.dmp LOGFILE=FULL_EXPORT.log FULL=Y

 

We can import the above HR schema directly into the test database.

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

On the other hand, we can speed up export and import  process by adding parallelism. Parallelism count depends on the number of cpu. For this, data pump technology allows us to speed up this process with parallel parameters. Parallelism can be given as follows. This number of parallelism should not more than the number of cpu of the physical server.

 

expdp \"/ as sysdba\" tables=HR.personel DIRECTORY=DATAPUMP DUMPFILE=personel%U.dmp LOGFILE=personel.log PARALLEL=8

 

One of the things that should be considered during the export import process is whether the data to be kept is in the same tablespaces. The tablespaces of tables to be moved are either created with the same name if the tablespaces are not exists in the target database. Or if the tablespaces are not exists in the target database, you can use remap tablespaces option to remap old tablespaces with new tablespaces.

We can provide this with the remap_tablespace parameter as follows. In the following example, while the HR schema in the source database is kept in the DATA01 tablespace, it will be kept in the existing DATA tablespace in the target database because there are no tablespace in the test database. We can provide this as follows.

 

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=replace REMAP_TABLESPACE=DATA01:DATA

 

 

Next article link is as follows.

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/