概述
寫這篇的初衷是因為之前都不怎么去記expdp的一些參數，用到的時候卻總發現一些命令給忘了，然后網上查卻怕不小心踏坑，所以花了點時間總結了下，以后就直接看這篇來找expdp方面的內容就行了。
一、基礎準備
1、創建導出目錄
create directory dpdata as '/home/oracle/dump'; select * from dba_directories where directory_name='DPDATA'; grant read,write on directory dpdata to hwb;
2、確保監聽沒問題
服務器監聽沒問題，可以通過 xx/xx來登錄數據庫
3、檢查導出前數據情況
SUM(s.BYTES)/1024/1024 "sizes(MB)" from s where s.owner='hwb';
count(*) from t where t.owner='hwb';
4、確定需要導出的用戶在哪些表空間，及其表初始化時占用的表空間大小
SELECT D.owner,D.tablespace_name, SUM(D.initial_extent)/1024/1024 initial_extent
FROM DBA_SEGMENTS D
WHERE D.owner IN
(SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN
('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))
GROUP BY D.tablespace_name,D.owner
ORDER BY initial_extent desc;
SELECT SUM(D.initial_extent)/1024/1024 initial_extent FROM DBA_SEGMENTS D WHERE D.owner IN
(SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));
由此可以知道，創建這些元數據大約需要6.8G的空間，如果涉及到數據的話，還需要判斷數據占用空間，不然執行導入的時候會因為表空間不足而不能導入
5、確定需要導出的用戶中有哪些無效的對象、及總共需要導出的對象數量
SELECT d.OWNER,d.status,count(1) FROM dba_objects d WHERE d.OWNER in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')) group by d.OWNER, d.status;
SELECT d.OWNER,d.OBJECT_NAME,d.OBJECT_TYPE,d.status FROM dba_objects d WHERE d.status = 'INVALID' and d.owner in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));
二、expdp命令
1、導出用戶
expdp scott/tiger@orcl =scott =expdp.dmp =;
2、導出表
expdp scott/tiger@orcl =emp,dept =expdp.dmp =;
3、按查詢條件導
expdp scott/tiger@orcl = =expdp.dmp =emp query='where =20';
4、按表空間導
expdp /@orcl = =.dmp =temp,;
5、導整個數據庫
expdp /@orcl = =full.dmp full=y;
6、利用=導出元數據
當設置為ALL 時,將導出對象定義及其所有數據.為時,只導出對象數據,為時,只導出對象定義
expdp hwb/hwb directory=DPDATA dumpfile=META.dmp logfile=META.log content=metadata_only schemas=TEST,SCOTT
7、并行導出scott用戶下所有的內容
expdp scott/tiger@orcl directory=DPDATA schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2
8、expdp腳本導出
expdp \'/ as sysdba\' parfile=parfile.ora
# vi parfile.ora
directory=xxxx
dumpfile=xxxx%U.dmp
filesize=20G
logfile=xxxx.log
tables=xxxx.xxxx
#schemas=xxxx
#tablespaces=xxxx
#full=y
content=ALL | DATA_ONLY | METADATA_ONLY
#EXCLUDE=object_type[:name_clause] [,….]
#estimate={BLOCKS | STATISTICS}
#ESTIMATE_ONLY=y
parallel=3
EXCLUDE=table:"IN('REPORT_DATA','FORMATDOC_DATA','CUSTOMER_FSRECORD','REPORT_RECORD')"
Exclude specific object types.
EXCLUDE=table:"IN('REPORT_DATA','FORMATDOC_DATA','CUSTOMER_FSRECORD','REPORT_RECORD')"
三、impdp命令
1、導入用戶（從用戶scott導入到用戶scott）
impdp scott/tiger@orcl = =expdp.dmp =scott;
2、導入表（從scott用戶中把表dept和emp導入到用戶中）
impdp /@orcl = =expdp.=scott.dept,scott.emp =scott:;
3、導入表空間
impdp /@orcl = =.dmp =;
4、導入數據庫
impdb /@orcl = =full.dmp full=y;
5、追加數據
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=systemtable_exists_action
四、測試驗證
1、刪除錯誤用戶下的包、存過和函數
執行如下的腳本刪除相應的錯誤對象：
BEGIN
FOR CUR IN (SELECT 'DROP ' || D.OBJECT_TYPE || ' ' || D.OBJECT_NAME SQLT FROM dba_objects d WHERE d.OWNER = 'NWPP_TEST'
AND d.CREATED >=to_date('2019-04-15 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND D.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')) LOOP
EXECUTE IMMEDIATE CUR.SQLT;
END LOOP;
END;
2、數據校驗
執行如下腳本和源庫作比較oracle 導出數據庫表結構oracle 導出數據庫表結構，查看數據是否完整。
SELECT d.OWNER,d.status,count(1) FROM dba_objects d WHERE d.OWNER in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')) group by d.OWNER, d.status;
SELECT d.OWNER,d.OBJECT_NAME,d.OBJECT_TYPE,d.status FROM dba_objects d WHERE d.status = 'INVALID' and d.owner in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));
這里主要做記錄用，所以會放命令比較多，部分有待驗證，大家看下有什么可以補充的地方，歡迎在下面留言探討。后面會分享更多DBA方面內容，感興趣的朋友可以關注下！