Oracle數(shù)據(jù)泵導入導出數(shù)據(jù)的實現(xiàn)

更新時間：2026年03月03日 09:37:19 作者：雷神樂樂

本文主要介紹了Oracle數(shù)據(jù)泵導入導出數(shù)據(jù)的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

一、導出/導入指定用戶下的表帶條件的數(shù)據(jù)

1.先通過Oracle視圖生成可執(zhí)行的SQL

SELECT 'ISS_A.' || table_name || ':"WHERE DATA_DATE IN (''20250630'',''20250731'')",'
FROM ALL_TABLES WHERE OWNER='ISS_A' AND TABLE_NAME LIKE 'ISS_A%';

2.新建一個par文件

注意：腳本里面如果不寫TABLES，寫SCHEMAS=ISS_A，就會導出ISS_A用戶下所有表過濾后的數(shù)據(jù)

vim exp-20250630.par

USERID=ISS_A/123456@127.0.0.1:1521/testdb
DUMPFILE=ISS_A_data_20250630.dmp
LOGFILE=ISS_A_data_20250630.log
TABLES=(
ISS_A.ISS_A_ACCT_INFO,
ISS_A.ISS_A_ACCT_INFO_BAK,
ISS_A.ISS_A_BD_BS_SM_DUTY_INFO,
ISS_A.ISS_A_BD_BS_SM_DUTY_INFO_BAK,
ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO,
ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO_BAK
)
CONTENT=DATA_ONLY
QUERY=(
ISS_A_ACCT_INFO:"WHERE DATA_DATE IN ('20250630','20250731')",
ISS_A_ACCT_INFO_BAK:"WHERE DATA_DATE IN ('20250630','20250731')",
ISS_A_BD_BS_SM_DUTY_INFO:"WHERE DATA_DATE IN ('20250630','20250731')",
ISS_A_BD_BS_SM_DUTY_INFO_BAK:"WHERE DATA_DATE IN ('20250630','20250731')",
ISS_A_BD_BS_SM_PUNISH_INFO:"WHERE DATA_DATE IN ('20250630','20250731')",
ISS_A_BD_BS_SM_PUNISH_INFO_BAK:"WHERE DATA_DATE IN ('20250630','20250731')"
)

3.使用數(shù)據(jù)泵執(zhí)行該par文件

[oracle@t-zt-db ~]$ expdp PARFILE=exp-20250630.par

4.查詢導出的行數(shù)與數(shù)據(jù)庫中的行數(shù)是否一致

SELECT * FROM ISS_A.ISS_A_ACCT_INFO WHERE DATA_DATE IN ('20250630','20250731');

是33行，說明無誤

5.使用數(shù)據(jù)泵導入dmp文件

清空原表指定日期的數(shù)據(jù)

DELETE FROM ISS_A.ISS_A_ACCT_INFO WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_ACCT_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');

注意：必須加上CONTENT=DATA_ONLY，否則表結構和數(shù)據(jù)都會導入執(zhí)行

impdp ISS_A/123456@127.0.0.1:1521/testdb DUMPFILE=ISS_A_data_20250630.dmp LOGFILE=imp_ISS_A_data_20250630.log CONTENT=DATA_ONLY

6.導入后查看數(shù)據(jù)量是否一致

SELECT 'SELECT * FROM ISS_A.' || table_name || ' WHERE DATA_DATE IN (''20250630'',''20250731'');,'
FROM ALL_TABLES WHERE OWNER='ISS_A' AND TABLE_NAME LIKE 'ISS_A%';

SELECT * FROM ISS_A.ISS_A_ACCT_INFO WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_ACCT_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');

到此這篇關于Oracle數(shù)據(jù)泵導入導出數(shù)據(jù)的文章就介紹到這了,更多相關Oracle數(shù)據(jù)泵導入導出內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: