一、导出/导入指定用户下的表带条件的数据
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.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.使用数据泵执行该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/123456@127.0.0.1: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');
到此这篇关于Oracle数据泵导入导出数据的文章就介绍到这了,
