ORACLE 10G 有新版的IMP程式稱為IMPDP,但怎麼用?有那些新的指令?
發現Oracle offical 網站:
Chapter 3, "Data Pump Import",介紹的很好,有新舊版指令對照,及一些參數的介紹跟範例,以下是自已截錄出來的重點,若有更詳細的還需要至上述網站查,不過都是原文的。

參數:TABLE_EXISTS_ACTION
這個參數當匯入時遇到table 已經存在了,該怎麼處理的參數,有四項:
TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}
是舊版IGRONE的取代加強版,預設為SKIP

SKIP
若是Table存在,則不匯入,跳至下個需處理的物件去處理.
若有CONTENT=DATA_ONLY參數,則此參數是無效的

APPEND
從DUMP檔載入資料至TABLE中。

TRUNCATE
先刪除(TRUNCATE)所有的資料,再從DUMP檔載入資料至TABLE中.

REPLACE
先drops 存在的TABLE ,再從DUMP檔中的DDL Create Table及載入資料至TABLE.
若有CONTENT=DATA_ONLY參數,則此參數是無效的

 

參數:SQLFILE
加入參數sqlfile=xxx.sql,則產生dmp內DDL script的SQL指令於xxx.SQL內
若以本例,就只會產生create table....等DDL script,不會產生insert into 資料的指令,
這在某些情況下,想核對dmp內的table structure or function等source與目前的是否一致
很有用吧!
有SQLFILE=參數,就只產生SQL file,並不會對資料庫做任何異動

Example
The following is an example of using the SQLFILE parameter.
You can create the expfull.dmp dump file used in this example
by running the example provided for the Export FULL parameter. See FULL.


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

 

DIRECTORY=dpump_dir1 這個指令到底dpump_dir1是在哪個目錄呢?
請下select * from ALL_DIRECTORIES; 就可以知道

 

參數:STATUS
這個指令之前就有了是FEEDBACK,可以顯示工作中的明細,現在指令是STATUS。

Example:
The following is an example of using the STATUS parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter. See
FULL.


> impdp hr/hr NOLOGFILE=y STATUS=120 DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

 

In this example, the status is shown every two minutes (120 seconds).


Example 3-1 Performing a Data-Only Table-Mode Import
只會匯入資料,因為指令CONTENT=DATA_ONLY


> impdp hr/hr TABLES=employees CONTENT=DATA_ONLY DUMPFILE=dpump_dir1:table.dmp
NOLOGFILE=y

 

Example 3-2 Performing a Schema-Mode Import

匯入指定的SCHEMA HR,並且排除匯入EXCLUDE=設定的物件,及若TABLE資料有重覆,則DROP TABLE後 CREATE AND LOAD DATA FROM DMP FILE。

> impdp hr/hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp
EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX TABLE_EXISTS_ACTION=REPLACE

 

Example 3-3 Network-Mode Import of Schemas


> impdp hr/hr TABLES=employees REMAP_SCHEMA=hr:scott DIRECTORY=dpump_dir1
NETWORK_LINK=dblink

 

這裡列出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_TABLESPACESparameter.
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.
文章標籤
創作者介紹
創作者 味味A 的頭像
味味A

味味A

味味A 發表在 痞客邦 留言(0) 人氣()