Expdp / Impdp 指令

expdp
Dbuser/Password	具有export 權限的User/密碼
directory=data_pump_dir1	expdp特有的，指定工作目錄
full=y	匯出全部的資料庫
Schemas=x,y,z	匯出指定的資料庫,不可跟full=y同時使用
TABLES=hr.employees	匯出指定的table 不可跟Schemas 同時使用
version=latest	相容舊版本的匯出(非必要)
compression=all	將匯出的dump file 壓縮 (11g後才有)
dumpfile=dbuser.dpdump	指定要匯入的dump file
logfile=dbuser.dplog	將log 寫在指定檔案(非必要)

impdp
Dbuser/Password	具有import 權限的User/密碼
schemas=x,y,z	匯入指定的資料庫
directory=data_pump_dir1	impdp特有的，指定工作目錄
TABLES=hr.employees	匯入指定的table
remap_schema=A:B	將A Schema 轉換成B Schema
remap_tablespace=T_A:T_B	將A Table Space 轉換成B Table Space

如何查詢 DIRECTORY

select * from dba_directories;

OWNER	DIRECTORY_NAME	DIRECTORY_PATH
SYS	ORACLE_OCM_CONFIG_DIR	/oracle/app/oracle/product/11.2.0/dbhome_1/ccr/state
SYS	XMLDIR	/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
SYS	DATA_PUMP_DIR1	/home/oracle
SYS	DATA_PUMP_DIR	/bkup/glcpdb_expdp

設定DIRECTORY

DROP DIRECTORY DATA_PUMP_DIR1;

CREATE OR REPLACE DIRECTORY

DATA_PUMP_DIR1 AS

'/home/oracle';

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR1 TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR1 TO IMP_FULL_DATABASE;

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR1 TO SYSTEM WITH GRANT OPTION;

DROP DIRECTORY DATA_PUMP_DIR;

CREATE OR REPLACE DIRECTORY

DATA_PUMP_DIR AS

'/bkup/glcpdb_expdp';

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO IMP_FULL_DATABASE;

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO SYSTEM WITH GRANT OPTION;

 GRANT EXP_FULL_DATABASE TO DBUSER;    將EXP_FULL_DATABASE 權限授與DBUSER