Detailed Documentation of EXPDP
Detailed Documentation of IMPDP
Data Pump is server-based, rather than client-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory path on the file system.
SQL> CREATE DIRECTORY data_pump AS 'C:\source’
After a directory is created, the user creating the directory object needs to grant READ or WRITE permission on the directory to other users.
SQL> GRANT READ, WRITE ON DIRECTORY data_pump TO hr;
EXPORT DATA PUMP
Estimating size of HR schema without exporting objects. The estimate is for table row data only and does not include metadata.
expdp fzafar/123@orcl logfile=estimate.log schemas=HR estimate_only=y
Exporting two schemas HR and SCOTT
expdp fzafar/123@orcl dumpfile=hr_scott.dmp logfile=hr_scott.log schemas=HR,SCOTT DIRECTORY=DATA_PUMP
expdp fzafar/123 DIRECTORY=data_pump DUMPFILE=hr_exclude.dmp EXCLUDE=VIEW,PACKAGE, FUNCTION
expdp fzafar/123@orcl DUMPFILE=HREXCLUDE.dmp logfile=HREXCLUDE.log schemas=HR DIRECTORY=DATA_PUMP CONTENT=DATA_ONLY EXCLUDE=TABLE:\”IN \(\’COUNTRIES\’,\’REGION\’\)\”
expdp fzafar/123@orcl PARFILE=exp.par
exp.par Parameter File
DIRECTORY=dpump_dir1
DUMPFILE=dataonly.dmp
CONTENT=DATA_ONLYPARALLEL=3
EXCLUDE=TABLE:"IN ('COUNTRIES', 'REGIONS')"
| EXP Parameter | EXPDP Parameter |
| OWNER | SCHEMAS |
| LOG | LOGFILE |
| STATISTICS=ESTIMATE | ESTIMATE=STATISTICS |
| ROWS | CONTENT |
| FEEDBACK | STATUS |
| FILE | DUMPFILE |
| CONSTRAINTS | EXCLUDE|INCLUDE=CONSTRAINTS |
| GRANTS | EXCLUDE|INCLUDE=GRANTS |
| INDEX | EXCLUDE|INCLUDE=INDEX |
| TRIGGERS | EXCLUDE|INCLUDE=TRIGGER |
ONLY AVAILABLE IN EXP
BUFFER, COMPRESS, DIRECT, INCTYPE, CONSISTENT, OBJECT_CONSISTENT, RECORDLENGTH, RESUMABLE, RESUMEABLE_TIMEOUT,USERID, VOLSIZE
ONLY AVAILABLE IN EXPDP
EXCLUDE, INCLUDE, ESTIMATE_ONLY, PARALLEL
IMPORT DATA PUMP
| IMP Parameter | IMPDP Paramater |
| FROMUSER | SCHEMAS |
| TOUSER | REMAP_SCHEMA |
| IGNORE | TABLE_EXIST_ACTION |
| INDEXFILE | SQLFILE |
| SHOW | SQLFILE |
Importing Only two tables from a schema
Schema Level Import
dumpfile=hr.dmp
DIRECTORY=data_pump
logfile=hr_dup_tbl.log
SCHEMAS=HR
REMAP_SCHEMA=HR:HR_DUP
INCLUDE=TABLE:”IN (‘REGIONS’,'LOCATIONS’)”
TABLE_EXISTS_ACTION=REPLACE
Table Level Import
dumpfile=hr.dmp
DIRECTORY=data_pump
logfile=hr_dup_tbl.log
REMAP_SCHEMA=HR:HR_DUP
TABLES=REGIONS,LOCATIONS
TABLE_EXISTS_ACTION=REPLACE
Example of using the SQLFILE parameter
impdp fzafar/123@orcl dumpfile=hr_scott.dmp DIRECTORY=data_pump SQLFILE=HR_SCOTT
Mapping two schemas HR and SCOTT to one schema HR_DUP (HR_DUP user should exist before impdp)
impdp fzafar/123@orcl parfile=’c:\source\import\imp_hrdup.txt’
dumpfile=hr_scott.dmp
DIRECTORY=data_pump
logfile=hr_dup_tbl.log
SCHEMAS=HR,SCOTT
REMAP_SCHEMA=HR:HR_DUP
REMAP_SCHEMA=SCOTT:HR_DUP
EXCLUDE=SEQUENCE
EXCLUDE=TABLE:”IN (‘HR.TEMP’)”
Importing Selected tables from export dump of two schemas
dumpfile=hr_scott.dmp
DIRECTORY=data_pump
CONTENT=ALL
logfile=hr_dup_tbl.log
REMAP_SCHEMA=HR:HR_DUP
TABLES=HR.REGIONS,HR.LOCATIONS
TABLE_EXISTS_ACTION=skip
Importing Only Tables that are not present already in Schema(Its a workaround not a good technique)
dumpfile=hr_scott.dmp
DIRECTORY=data_pump
CONTENT=ALL
logfile=hr_dup_tbl.log
REMAP_SCHEMA=HR:HR_DUP
REMAP_SCHEMA=SCOTT:HR_DUP
EXCLUDE=sequence
EXCLUDE=procedure
EXCLUDE=TRIGGER
EXCLUDE=view
TABLE_EXISTS_ACTION=skip
STATUS=0
JOB_NAME=’imp_hr_dup’
