需要建立DIRECTORY
expdp,impdp是新的資料匯出,匯入工具,是基於server端的工具,所有的檔案需要在伺服器端存放,這是有別與舊的匯出/匯入工具(exp,imp).
建立的語句: create or replace directory dmp_dir as '/dmp';
給使用者賦權: grant read,write on dmp_dir to user1,user2;

[@more@]

我們可以為資料檔案和日誌檔案建立不同的directory
create ora replace directory dmp_log as '/dmplog/';
匯出資料時,將資料檔案與日誌檔案存放到不同的地方
expdp user1/***** directory=dmp_dir dumpfile=user1.dmp logfile=dmp_log:user1.log
SQL>SELECT directory_name, directory_path FROM dba_directories ;
查詢目前已經定義的DIRECTORY.
匯出,匯入的檔案集合的名稱可以自動生成,使用%U
expdp user1/**** directory=dmp_dir dumpfile=user1_%U.dmp filesize=2g logfile=user1.log
這樣可以自動從01開始編號,避免了舊工具匯出時檔名稱重複的問題.

新舊匯出工具的操作有所不同,下面摘錄了具體的解釋,來自與ORACLE的官方文件.
Table 3-2 Original Import Parameters and Their Counterparts in Data Pump Import

Original Import Parameter	Comparable Data Pump Import Parameter
BUFFER	A parameter comparable to BUFFER is not needed.
CHARSET	A parameter comparable to CHARSET is not needed.
COMMIT	A parameter comparable to COMMIT is not supported.
COMPILE	A parameter comparable to COMPILE is not supported.
CONSTRAINTS	EXCLUDE=CONSTRAINT
DATAFILES	TRANSPORT_DATAFILES
DESTROY	REUSE_DATAFILES
FEEDBACK	STATUS
FILE	DUMPFILE
FILESIZE	Not necessary. It is included in the dump file set.
FROMUSER	SCHEMAS
FULL	FULL
GRANTS	EXCLUDE=GRANT
HELP	HELP
IGNORE	TABLE_EXISTS_ACTION
INDEXES	EXCLUDE=INDEX
INDEXFILE	SQLFILE with INCLUDE INDEX
LOG	LOGFILE
PARFILE	PARFILE
RECORDLENGTH	A parameter comparable to RECORDLENGTH is not needed.
RESUMABLE	A parameter comparable to RESUMABLE is not needed. It is automatically defaulted for privileged users.
RESUMABLE_NAME	A parameter comparable to RESUMABLE_NAME is not needed. It is automatically defaulted for privileged users.
RESUMABLE_TIMEOUT	A parameter comparable to RESUMABLE_TIMEOUT is not needed. It is automatically defaulted for privileged users.
ROWS=N	CONTENT=METADATA_ONLY
ROWS=Y	CONTENT=ALL
SHOW	SQLFILE
SKIP_UNUSABLE_INDEXES	SKIP_UNUSABLE_INDEXES
STATISTICS	A parameter comparable to STATISTICS is not needed. If the source table has statistics, they are imported.
STREAMS_CONFIGURATION	STREAMS_CONFIGURATION
STREAMS_INSTANTIATION	A parameter comparable to STREAMS_INSTANTIATION is not needed.
TABLES	TABLES
TABLESPACES	This parameter still exists, but some of its functionality is now performed using the TRANSPORT_TABLESPACES parameter.
TOID_NOVALIDATE	A command comparable to TOID_NOVALIDATE is not needed. OIDs are no longer used for type validation.
TOUSER	REMAP_SCHEMA
TRANSPORT_TABLESPACE	TRANSPORT_TABLESPACES (see command description)
TTS_OWNERS	A parameter comparable to TTS_OWNERS is not needed because the information is stored in the dump file set.
USERID	A parameter comparable to USERID is not needed. This information is supplied as the username/password when you invoke Import.
VOLSIZE	A parameter comparable to VOLSIZE is not needed because tapes are not supported.

使用INCLUDE,EXCLUDE引數來實現更加靈活的物件篩選操作.

INCLUDE 是包含且僅包含的物件條件;EXCLUDE 是排除的物件條件.

INCLUDE,EXCLUDE 的條件表示式在windows環境裡不需要輸入跳脫字元,在UNIX/LINUX環境下一定需要使用跳脫字元'/';下面透過linux環境下的幾個例子來說明一下INCLUDE,EXCLUDE的使用方法.

Syntax and Description

EXCLUDE=object_type[:name_clause] [, ...]

Name_clause是一個SQL 表示式語句,大致可以有下面幾個格式:

EXCLUDE=SCHEMA:"='HR'"

EXCLUDE=VIEW

EXCLUDE=PACKAGE

EXCLUDE=FUNCTION

EXCLUDE=TABLE

EXCLUDE=INDEX

EXCLUDE=PROCEDURE

我們可以使用'=' 'LIKE' 'IN()'等方式來書寫表示式,舉幾個例子:

EXCLUDE=SCHEMA:"='HR'"

LINUX格式: EXCLUDE=SCHEMA:/"/=/'HR/'/"

匯出時不包括HR使用者的資料

EXCLUDE=TABLE:" LIKE 'EMP_%'"

UNIX格式:EXCLUDE=TABLE:/" LIKE /'EMP_%/'/"

匯出是不包括表名稱以'EMP_'開頭的表,例如不匯出 EMP_USERS等等.

EXCLUDE=INDEX:" IN ('PK_EMP','PK_CUST','PK_WORK')"

UNIX格式: EXCLUDE=INDEX:/" IN /(/'PK_EMP/',/'PK_CUST/',/'PK_WORK/'/)/"

匯出不包括PK_EMP,PK_CUST,PK_WORK的索引

EXCLUDE=VIEW,PACKAGE, FUNCTION

匯出是不包括VIEW,PACKAGE,FUNCTION等型別的物件

Expdp,impdp工具的使用

相關文章