Why and How to export using a Data Pump utility?

  Why ?

In oracle database to move a data between systems or duplicate a schema in the same system there are several ways to achieve it. One of the common way to do this data migration and/or duplication you may use the Data Pump utility in the command line of the server and or the client with a full administration client installation. Below I will share a sample scripts that export and import using this utility, for advantage and features of this you may refer back to the oracle documentations, However on of the most important feature is that cross platform can be easily maintains by using this.

How ?

Below are sample script to do export and import using data pump ( dp), you have to create or use a user rather than sys, here I am using a system user and have created a Backup directory in OS/Oracle ,:

1- you may run export and import in one line with all necessary values as below, dmp will have the date and .log is the export/import logs, you may rename the import using REMAP as below example:

expdp system/<password>@<db_tns_name> directory=BACKUP schemas=<schema1>,<schema2> dumpfile=<name>.dmp logfile=<name>.log

impdp system/<password>@<db_tns_name> REMAP_SCHEMA=<old_name>:<new_name>,<old_name2>:<new_name2> directory=BACKUP dumpfile=<name>.dmp logfile=<name>.log

2- Export using parameter file with further export features as below example for certain tables of schame:

vi <parm_name>.par

 

DUMPFILE="<name>.dmp"

LOGFILE="<name>.log"

LOGTIME=NONE

DIRECTORY=BACKUP

FLASHBACK_TIME=SYSTIMESTAMP

COMPRESSION=METADATA_ONLY

CONTENT=ALLCOMPRESSION_ALGORITHM=BASIC

TABLES=('<schema."<table_name1>"', '<schema."<table_name2>"', '<schema>."<>table_name3"')

 

expdp system/<password>@<tns_name> PARFILE= <parm_name>.par

3- Import the export dump as needed.