EXPDP/IMPDP

ABORT_STEP

Used to stop the job after it is initialized. This allows the master table to be queried

before any data is exported.

Syntax

ABORT_STEP=[n | -1] Default: Null

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr ABORT_STEP=-1

impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log DUMPFILE=expdat.dmp ABORT_STEP=-1

ACCESS_METHOD

Instructs Export to use a particular method to unload data.

Syntax

ACCESS_METHOD=[AUTOMATIC | DIRECT_PATH | EXTERNAL_TABLE] Default: AUTOMATIC

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr ACCESS_METHOD=EXTERNAL_TABLE

impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log DUMPFILE=expdat.dmp ACCESS_METHOD=CONVENTIONAL

ATTACH

Attach to an existing job

Syntax

ATTACH [=[schema_name.]job_name] Default: job currently in the user’s schema, if there is only one

Example

expdp hr ATTACH= export_job

impdp hr ATTACH=import_job

CLUSTER

Determines whether Data Pump can use Oracle Real Application Clusters (Oracle

RAC) resources and start workers on other Oracle RAC instances.

Syntax

CLUSTER=[YES | NO] Default: YES

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_clus%U.dmp CLUSTER=NO PARALLEL=3

impdp hr DIRECTORY=dpump_dir1 SCHEMAS=hr CLUSTER=NO PARALLEL=3 NETWORK_LINK=dbs1

COMPRESSION

Reduce the size of a dumpfile.

Syntax

COMPRESSION=[ALL | DATA_ONLY | METADATA_ONLY | NONE] Default: METADATA_ONLY

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_comp.dmp COMPRESSION=METADATA_ONLY

COMPRESSION_ALGORITHM

Specifies the compression algorithm to be used when compressing dump file data.

Syntax

COMPRESSION_ALGORITHM = {BASIC | LOW | MEDIUM | HIGH} Default: BASIC

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=usef.dmp COMPRESSION=DATA_ONLY

COMPRESSION_ALGORITHM=LOW

OR

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=usef.dmp COMPRESSION=ALL COMPRESSION_ALGORITHM=BASIC

CONTENT

Specifies data to unload.

Syntax

CONTENT=[ALL | DATA_ONLY | METADATA_ONLY] Default: ALL

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=usef.dmp CONTENT=METADATA_ONLY

DATA_OPTIONS

The DATA_OPTIONS parameter designates how certain types of data should be handled

during export/import operations.

Syntax

DATA_OPTIONS=XML_CLOBS Default: There is no default.

Example

expdp hr TABLES=usef.xdb_tab1 DIRECTORY=dpump_dir1 DUMPFILE=hr_xml.dmp VERSION=11.2 DATA_OPTIONS=XML_CLOBS

impdp hr TABLES=employees CONTENT=DATA_ONLY DUMPFILE=dpump_dir1:table.dmp DATA_OPTIONS=skip_constraint_errors

DIRECTORY

Directory object to be used for dumpfiles and logfiles.

Syntax

DIRECTORY=directory_object  Default: DATA_PUMP_DIR

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=employees.dmp CONTENT=METADATA_ONLY

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=dpump_dir2:expfull.log

DUMPFILE

List of destination dump files

Syntax

DUMPFILE=[directory_object:]file_name [, …] Default: expdat.dmp

Example

expdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir2:exp1.dmp, exp2%U.dmp PARALLEL=3

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir2:exp1.dmp, exp2%U.dmp

ENCRYPTION

Encrypt part or all of a dump file.

Syntax

ENCRYPTION = [ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_enc.dmp JOB_NAME=enc1 ENCRYPTION=data_only ENCRYPTION_PASSWORD=usef

ENCRYPTION_ALGORITHM

Specify how encryption should be done.

Syntax

ENCRYPTION_ALGORITHM = [AES128 | AES192 | AES256] Default: AES128

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_enc3.dmp ENCRYPTION_PASSWORD=foobar ENCRYPTION_ALGORITHM=AES128

ENCRYPTION_MODE

Method of generating encryption key.

Syntax

ENCRYPTION_MODE = [DUAL | PASSWORD | TRANSPARENT]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_enc4.dmp ENCRYPTION=all ENCRYPTION_PASSWORD=secretwords ENCRYPTION_ALGORITHM=AES256 ENCRYPTION_MODE=DUAL

ENCRYPTION_PASSWORD

Password key for creating encrypted data within a dump file.

Syntax

ENCRYPTION_PASSWORD = password Default: There is no default

Example

expdp hr TABLES=employee_s_encrypt DIRECTORY=dpump_dir1 DUMPFILE=dpcd2be1.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY  ENCRYPTION_PASSWORD=123456

impdp hr TABLES=employee_s_encrypt DIRECTORY=dpump_dir  DUMPFILE=dpcd2be1.dmp ENCRYPTION_PASSWORD=123456

ENCRYPTION_PWD_PROMPT

Specifies whether Data Pump should prompt you for the encryption password.

Syntax

ENCRYPTION_PWD_PROMPT=[YES | NO] Default: NO

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=usef.dmp ENCRYPTION_PWD_PROMPT=YES

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=usef.dmp ENCRYPTION_PWD_PROMPT=YES

ESTIMATE

Calculate job estimates.

Syntax

ESTIMATE=[BLOCKS | STATISTICS] Default: BLOCKS

Example

expdp hr TABLES=employees ESTIMATE=STATISTICS DIRECTORY=dpump_dir1 DUMPFILE=estimate_stat.dmp

impdp hr TABLES=job_history NETWORK_LINK=source_database_link  DIRECTORY=dpump_dir1 ESTIMATE=STATISTICS

ESTIMATE_ONLY

Calculate job estimates without performing the export.

Syntax

ESTIMATE_ONLY=[YES | NO] Default: NO

Example

expdp hr ESTIMATE_ONLY=YES NOLOGFILE=YES SCHEMAS=HR

EXCLUDE

Exclude specific object types.

Syntax

EXCLUDE=object_type[:name_clause] [, …]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_exclude.dmp EXCLUDE=VIEW,PACKAGE, FUNCTION

OR

EXCLUDE=FUNCTION

EXCLUDE=PROCEDURE

EXCLUDE=PACKAGE

EXCLUDE=INDEX:”LIKE ‘EMP%’ “

impdp system DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp PARFILE=exclude.par

FILESIZE

Specify the size of each dumpfile in units of bytes.

Syntax

FILESIZE=integer[B | KB | MB | GB | TB] Default: 0

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_3m.dmp FILESIZE=3MB

FLASHBACK_SCN

SCN used to reset session snapshot.

Syntax

FLASHBACK_SCN=scn_value

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmp FLASHBACK_SCN=384632

impdp hr DIRECTORY=dpump_dir1 FLASHBACK_SCN=123456 NETWORK_LINK=source_database_link

FLASHBACK_TIME

Time used to find the closest corresponding SCN value.

Syntax

FLASHBACK_TIME=”TO_TIMESTAMP(time-value)”

Example

DIRECTORY=dpump_dir1

DUMPFILE=hr_time.dmp

FLASHBACK_TIME=”TO_TIMESTAMP(’27-10-2012 13:16:00′, ‘DD-MM-YYYY HH24:MI:SS’)”

impdp hr DIRECTORY=dpump_dir1 PARFILE=flashback_imp.par NETWORK_LINK=source_

database_link

FULL

Export/import entire database .

Syntax

FULL=[YES | NO] Default: NO

Example

expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=YES  NOLOGFILE=YES

impdp hr DUMPFILE=dpump_dir1:expfull.dmp FULL=YES  LOGFILE=dpump_dir2:full_imp.log

HELP

Displays online help for the Export/import utility.

Syntax

HELP = [YES | NO]

Example

expdp HELP = YES

impdp HELP = YES

INCLUDE

Include specific object types.

Syntax

INCLUDE = object_type[:name_clause] [, …]

Example

SCHEMAS=HR

DUMPFILE=expinclude.dmp

DIRECTORY=dpump_dir1

LOGFILE=expinclude.log

INCLUDE=TABLE:”IN (‘EMPLOYEES’, ‘DEPARTMENTS’)”

INCLUDE=PROCEDURE

INCLUDE=INDEX:”LIKE ‘EMP%'”

expdp hr PARFILE=usef.par

OR

expdp hr INCLUDE=TABLE DUMPFILE=dpump_dir1:exp_inc.dmp NOLOGFILE=YES

impdp system SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp PARFILE=imp_include.par

JOB_NAME

Name of export/import  job

Syntax

JOB_NAME=jobname_string

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME=exp_job NOLOGFILE=YES

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp JOB_NAME=impjob01

KEEP_MASTER

Indicates whether the master table should be deleted or retained at the end of a Data

Pump job that completes successfully. The master table is automatically retained for

jobs that do not complete successfully.

Syntax

KEEP_MASTER=[YES | NO] Default: NO

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr KEEP_MASTER=YES

impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log DUMPFILE=expdat.dmp KEEP_MASTER=YES

LOGFILE

Specifies the name, and optionally, a directory, for the log file of the export/import  job.

Syntax

LOGFILE=[directory_object:]file_name

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=usef.dmp LOGFILE=hr_export.log

impdp hr SCHEMAS=HR DIRECTORY=dpump_dir2 LOGFILE=imp.log DUMPFILE=dpump_dir1:expfull.dmp

LOGTIME

Specifies that messages displayed during export/import operations be timestamped.

Syntax

LOGTIME=[NONE | STATUS | LOGFILE | ALL]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr LOGTIME=ALL

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr LOGTIME=ALL TABLE_EXISTS_ACTION=REPLACE

METRICS

Indicates whether additional information about the job should be reported to the Data

Pump log file.

Syntax

METRICS=[YES | NO]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr METRICS=YES

impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log DUMPFILE=expdat.dmp METRICS=YES

NETWORK_LINK

Name of remote database link to the source system.

Syntax

NETWORK_LINK=source_database_link

Example

expdp hr DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link DUMPFILE=network_export.dmp LOGFILE=network_export.log

impdp hr TABLES=employees DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link EXCLUDE=CONSTRAINT

NOLOGFILE

Specifies whether to suppress creation of a log file.

Syntax

NOLOGFILE=[YES | NO] Default: NO

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=usef.dmp NOLOGFILE=YES

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp NOLOGFILE=YES

PARALLEL

Change the number of active workers for current job.

Syntax

PARALLEL=integer Default: 1

Example

expdp hr DIRECTORY=dpump_dir1 LOGFILE=parallel_export.log  JOB_NAME=par4_job DUMPFILE=par_exp%u.dmp PARALLEL=4

impdp hr DIRECTORY=dpump_dir1 LOGFILE=parallel_import.log  JOB_NAME=imp_par3 DUMPFILE=par_exp%U.dmp PARALLEL=3

PARFILE

Specify parameter file name.

Syntax

PARFILE=[directory_path]file_name Default: There is no default

Example

SCHEMAS=HR

DUMPFILE=exp.dmp

DIRECTORY=dpump_dir1

LOGFILE=exp.log

expdp hr PARFILE=usef.par

impdp hr PARFILE=hr_imp.par

QUERY

Predicate clause used to export/import a subset of a table.

Syntax

QUERY = [schema.][table_name:] query_clause Default: There is no default

Example

QUERY=’sales:”WHERE EXISTS (SELECT cust_id FROM customers c WHERE cust_credit_limit > 10000 AND ku$.cust_id = c.cust_id)”‘

OR

QUERY=’sales:”WHERE EXISTS (SELECT cust_id FROM customers c WHERE cust_credit_limit > 10000 AND cust_id = c.cust_id)”‘

expdp hr PARFILE=emp_query.par

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp  PARFILE=query_imp.par NOLOGFILE=YES

REMAP_DATA

Specify a data conversion function.

Syntax

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

Example

 expdp hr DIRECTORY=dpump_dir1 DUMPFILE=remap1.dmp TABLES=employees REMAP_DATA=usef.employees.employee_id:usef.remap.minus10 REMAP_DATA=usef.employees.first_name:usef.remap.plusx

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmpTABLES=hr.employees REMAP_DATA=hr.employees.first_name:hr.remap.plusx

REUSE_DUMPFILES

Overwrite destination dump file if it exists

Syntax

REUSE_DUMPFILES=[YES | NO]

Example

 expdp hr DIRECTORY=dpump_dir1 DUMPFILE=enc1.dmp TABLES=employees REUSE_DUMPFILES=YES

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=reuse.log REUSE_DATAFILES=YES

REMAP_DATAFILE

Redefine datafile references in all DDL statements.

Syntax

REMAP_DATAFILE=source_datafile:target_datafile

Example

DIRECTORY=dpump_dir1

FULL=YES

DUMPFILE=db_full.dmp

REMAP_DATAFILE=”‘DB1$:[HRDATA.PAYROLL]tbs6.dbf’:’/db1/hrdata/payroll/tbs6.dbf'”

impdp hr PARFILE=payroll.par

REMAP_SCHEMA 

Objects from one schema are loaded into another schema.

Syntax

REMAP_SCHEMA=source_schema:target_schema

Example

impdp system DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

REMAP_TABLE

Table names are remapped to another table.

Syntax

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

or

REMAP_TABLE=[schema.]old_tablename[:partition]:new_tablename

Example

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp TABLES=usef.employees REMAP_TABLE=usef.employees:emps

REMAP_TABLESPACE

Tablespace object are remapped to another tablespace.

Syntax

REMAP_TABLESPACE=source_tablespace:target_tablespace

Example

impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY=dpump_dir1  DUMPFILE=employees.dmp

SQLFILE

Write all the SQL DDL to a specified file.

Syntax

SQLFILE=[directory_object:]file_name

Example

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:expfull.sql

TABLE_EXISTS_ACTION

Action to take if imported object already exists.

Syntax

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

Example

impdp hr TABLES=employees DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLE_EXISTS_ACTION=REPLACE

SAMPLE

Percentage of data to be exported.

Syntax

SAMPLE=”HR”.”EMPLOYEES”:50

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=sample.dmp SAMPLE=70

SCHEMAS

List of schemas to export/import.

Syntax

SCHEMAS=schema_name [, …]

Example

 expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe

impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log DUMPFILE=expdat.dmp

SERVICE_NAME

Used to specify a service name to be used in conjunction with the CLUSTER parameter.

Syntax

SERVICE_NAME=name

Example

 expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_svname2.dmp SERVICE_NAME=sales

impdp system DIRECTORY=dpump_dir1 SCHEMAS=hr  SERVICE_NAME=sales NETWORK_LINK=dbs1

SOURCE_EDITION

Edition to be used for extracting metadata

Syntax

SOURCE_EDITION=edition_name

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=exp_dat.dmp SOURCE_EDITION=exp_edition EXCLUDE=USER

impdp hr DIRECTORY=dpump_dir1 SOURCE_EDITION=exp_edition NETWORK_LINK=source_database_link EXCLUDE=USER

STATUS

Specifies the frequency at which the job status display is updated.

Syntax

STATUS=[integer]

Example

expdp hr DIRECTORY=dpump_dir1 SCHEMAS=hr,sh STATUS=300

impdp hr NOLOGFILE=YES STATUS=120 DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

SKIP_UNUSABLE_INDEXES

Specifies whether Import skips loading tables that have indexes that were set to the

Index Unusable state.

Syntax

SKIP_UNUSABLE_INDEXES=[YES | NO]

Example

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=skip.log SKIP_UNUSABLE_INDEXES=YES

TABLES

Specifies that you want to perform a table-mode export/import.

Syntax

TABLES=[schema_name.]table_name[:partition_name] [, …]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=employees,jobs,departments

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs

TABLESPACES

Specifies a list of tablespace names to be exported/imported in tablespace mode.

Syntax

TABLESPACES=tablespace_name [, …]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp TABLESPACES=tbs_4, tbs_5, tbs_6

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLESPACES=tbs_1,tbs_2,tbs_3,tbs_4

TRANSPORT_FULL_CHECK

Verify storage segments of all tables

Syntax

TRANSPORT_FULL_CHECK=[YES | NO]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp  TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=YES LOGFILE=tts.log

impdp hr PARFILE=full_check.par

TRANSPORT_TABLESPACES

Specifies that you want to perform an export/import in transportable-tablespace mode.

Syntax

TRANSPORT_TABLESPACES=tablespace_name [, …]

Example

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp TRANSPORT_TABLESPACES=tbs_1

DIRECTORY=dpump_dir1

NETWORK_LINK=source_database_link

TRANSPORT_TABLESPACES=tbs_6

TRANSPORT_FULL_CHECK=NO

TRANSPORT_DATAFILES=’user01/data/tbs6.dbf’

impdp hr PARFILE=tablespaces.par

TRANSPORTABLE

Specify whether transportable method can be used.

Syntax

TRANSPORTABLE = [ALWAYS | NEVER]

Example

expdp sh DIRECTORY=dpump_dir1 DUMPFILE=tto1.dmpTABLES=sh.sales2 TRANSPORTABLE=ALWAYS

impdp import_admin FULL=Y TRANSPORTABLE=ALWAYS VERSION=12 NETWORK_LINK=dbs1  ENCRYPTION_PASSWORD=password TRANSPORT_DATAFILES=<datafile_name>   LOGFILE=dpump_dir1:fullnet.log

VERSION

Version of objects to export/import.

Syntax

VERSION=[COMPATIBLE | LATEST | version_string]

Example

expdp hr TABLES=usef.employees VERSION=LATEST DIRECTORY=dpump_dir1 DUMPFILE=emp.dmp NOLOGFILE=YES

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees VERSION=LATEST

VIEWS_AS_TABLES

Specifies that one or more views are to be exported/imported as tables.

Syntax

VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], … Default: There is no default

Example

expdp scott/tiger views_as_tables=view1 directory=data_pump_dir dumpfile=scott1.dmp

impdp hr VIEWS_AS_TABLES=view1:view1_tab NETWORK_LINK=dblink1

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد.