Oracle資料泵的備份與恢復

1、建立DIRECTORY

create directory sjhf as 'E:\sjhf';

Grant read,write on directory sjhf to test;

2、檢視目錄及許可權

SELECT privilege, directory_name, directory_path

  FROM user_tab_privs t, all_directories d

 WHERE t.table_name(+) = d.directory_name

 ORDER BY 2, 1;

3、執行匯出

expdp test/passwd@orcl version=10.2.0.1 directory=sjhf dumpfile =test.dmp 

通用寫法

expdp test/passwd@orcl version=10.2.0.1 dumpfile=test%mydate%.dmp directory=sjhf logfile=test%mydate%.log

forfiles /p "E:\sjhf" /s /m *.dmp /d -1 /c "cmd /c del @file"

4、執行匯入

先將要恢復的dmp檔案放置E:\sjhf目錄下，例如：test.dmp

impdp test/passwd@orcl directory=sjhf dumpfile=test.dmp

5、如果只想匯出特別表名的表

--LINUX下

expdp scott/tiger directory=sjhf dumpfile=scott.dmp include=table:\"LIKE \'%EMP%\'\"

-Windows下

expdp scott/tiger directory=sjhf dumpfile=scott.dmp include=table:\"LIKE '%EMP%'\"