EXPDP and IMPDP

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_ONLY
PARALLEL=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’

Advertisements

6 responses to this post.

  1. Posted by Randy on March 8, 2010 at 2:51 pm

    Morning,

    I found your IMPDP with Includes quite informative.

    We are executing IMPDP using an EXPDP FULL DMP which has many schemas.

    Content=Data_only
    Table_exists_action=Truncate
    Remap_schema=A:B
    Include=table:”in(select tb_name from migration_table)

    This appears to work however if 2 schemas in the DMP file have the same table_name, both schema.tables are imported and we wish to only have the schema A imported.

    So we tried to execute from your example:

    Content=Data_only
    Table_exists_action=Truncate
    Remap_schema=A:B
    Include=table:”in(‘A.tablename’)”

    And no tables are imported at all??

    Seems strange as this is the exact syntax you quoted in your Mapping two schemas example.

    Our ultimate goal is to perform this second IMPDP using some form Include=table:”IN(select from the migration table in example 1 above since the table list is > 1000 tables and vioates the 4000 byte limit of the parameter.

    Any assistance would be greatly appreciated.

    Thanks,

    Randy R. Creller

    Reply

  2. Posted by Alex on May 7, 2010 at 11:45 pm

    EXCLUDE=SCHEMA:\”=\ ‘schema_name\’ \”

    OR

    try to include only schema A

    Reply

  3. Posted by mohibalvi on May 13, 2010 at 9:42 pm

    Thanks Alex for your response

    Reply

  4. No way to disable datapump estimate?…

    昨天晚上去客户现场配合一个导入导出作业;这是一套Solaris 10上的10.2.0.1系统,导出采用expdp数据泵工具,需要导出的数据源是一张大小在120G左右的单表,该表存放了最近一年的数据,实际导出时只需要最近三个月的数据,所以使用了QUERY选项,并指定了并行度为2。 该导出作业之前未经测试过,语句也是临时写的,实际执行导出工作时发现在评估阶段(estimate phase)耗费了大约十多分钟的时间,estimate默认使用blocks模式,即通过计算对象段占用的数据库块来预估dumpfi…

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: