快捷導(dǎo)航

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

更新時(shí)間：2026年03月03日 09:37:19 作者：雷神樂(lè)樂(lè)

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

1.先通過(guò)Oracle視圖生成可執(zhí)行的SQL
2.新建一個(gè)par文件
3.使用數(shù)據(jù)泵執(zhí)行該par文件
4.查詢導(dǎo)出的行數(shù)與數(shù)據(jù)庫(kù)中的行數(shù)是否一致
5.使用數(shù)據(jù)泵導(dǎo)入dmp文件
6.導(dǎo)入后查看數(shù)據(jù)量是否一致

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

1.先通過(guò)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.新建一個(gè)par文件

注意：腳本里面如果不寫(xiě)TABLES，寫(xiě)SCHEMAS=ISS_A，就會(huì)導(dǎo)出ISS_A用戶下所有表過(guò)濾后的數(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.查詢導(dǎo)出的行數(shù)與數(shù)據(jù)庫(kù)中的行數(shù)是否一致

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

是33行，說(shuō)明無(wú)誤

5.使用數(shù)據(jù)泵導(dǎo)入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，否則表結(jié)構(gòu)和數(shù)據(jù)都會(huì)導(dǎo)入執(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.導(dǎo)入后查看數(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');

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

您可能感興趣的文章: