Oracle数据泵实现数据的导入导出

一、导出/导入指定用户下的表带条件的数据

1.先通过Oracle视图生成可执行的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用户下所有表过滤后的数据

vim exp-20250630.parUSERID=ISS_A/[email protected]:1521/testdbDUMPFILE=ISS_A_data_20250630.dmpLOGFILE=ISS_A_data_20250630.logTABLES=(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_ONLYQUERY=(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.使用数据泵执行该par文件

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

4.查询导出的行数与数据库中的行数是否一致

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

是33行，说明无误

5.使用数据泵导入dmp文件

清空原表指定日期的数据

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，否则表结构和数据都会导入执行

impdp ISS_A/[email protected]:1521/testdb DUMPFILE=ISS_A_data_20250630.dmp LOGFILE=imp_ISS_A_data_20250630.log CONTENT=DATA_ONLY

6.导入后查看数据量是否一致

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');