Oracle Data Pump is faster and more flexible alternative to the “exp” and “imp” backup system used in previous Oracle versions. impdp and expdp commands use Oracle Directory for processing of backups.
Create Directory
CONN SYS AS SYSDBA
CREATE OR REPLACE DIRECTORY dirname AS ‘/u01/backup/’;
GRANT READ, WRITE ON DIRECTORY dirname TO backupuser;
Tables Export and Import
expdp backupuser/password@db tables=EMPLOYEES,COUNTRY directory=dirname dumpfile=backupdumpfilename.dmp logfile=logfilenameforexp.log
impdp backupuser/password@db tables=EMPLOYEES,COUNTRY directory=dirname dumpfile=backupdumpfilename.dmp logfile=logfilenameforimp.log
Schema Export and Import
expdp backupuser/password@db schemas=HR directory=dirname dumpfile=backupdumpfilename.dmp logfile=logfilenameforexp.log
impdp backupuser/password@db schemas=HR directory=dirname dumpfile=backupdumpfilename.dmp logfile=logfilenameforimp.log
Remap Schema
impdp backupuser/password@db directory=dirname remap_schema=SourceSchema:TargetSchema dumpfile=fullbackupdumpfilename.dmp logfile=logfilenameforimp.log
Table Export and Import From Full Backup With include and exclude
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import.
impdp backupuser/password@db schemas=HR include=TABLE:”IN (‘TBL1’, ‘TBL2’)” directory=dirname dumpfile=backupdumpfilename.dmp logfile=logfilenameforimp.log
impdp backupuser/password@db schemas=HR exclude=TABLE:”= ‘TBL5′” directory=dirname dumpfile=backupdumpfilename.dmp logfile=logfilenameforimp.log
Import Only One Table From Full Backup With Remap
impdp backupuser/password@db schemas=HR remap_schema=SourceSchema:TargetSchema include=TABLE:”IN (‘TBL1’, ‘TBL2’)” directory=dirname dumpfile=backupdumpfilename.dmp logfile=logfilenameforimp.log
Full Export and Import
expdp backupuser/password@db full=y directory=dirname dumpfile=fullbackupdumpfilename.dmp logfile=logfilenameforexp.log
impdp backupuser/password@db full=y directory=dirname dumpfile=fullbackupdumpfilename.dmp logfile=logfilenameforimp.log
After taking a full backup, you can turn from full backup as schema by schema.
impdp system/password@db schemas=HR directory=dirname dumpfile=fullupdumpfilename.dmp logfile=logfilenameforimp.log
Can select statements output be sent to the datapump directly with out the need for a table .My requirement is to select data on the fly from tables based on some logic and output the data to expdp (table wise ) . At the target site the data will be imported into existing table