expdp/impdp解决方案—MES数据库主键丢失的解决方法

1、业务正常的MES库的主键数量(用户是MES_PRD）-192.168.20.50

select count(*) from 
(
SELECT c.* FROM dba_cons_columns c
JOIN dba_constraints k ON c.owner = k.owner AND c.constraint_name = k.constraint_name
WHERE k.constraint_type = 'P' and k.owner='MES_PRD' 
ORDER BY c.owner, c.table_name, c.position
);

2、缺失主键的库主键数量(用户是MES_PRD）-192.168.20.222

select count(*) from 
(
SELECT c.* FROM dba_cons_columns c
JOIN dba_constraints k ON c.owner = k.owner AND c.constraint_name = k.constraint_name
WHERE k.constraint_type = 'P' and k.owner='MES_PRD' 
ORDER BY c.owner, c.table_name, c.position
);

3、导出正常库的所有约束（MES_PRD用户下）-192.168.20.50

expdp mes_prd/mes800 dumpfile=exp_constraints-20230626.dmp logfile=exp_constraints.log directory=backup_mes include=constraint

4、传输dump文件-192.168.20.222

scp -r exp_constraints-20230626.dmp oracle@192.168.20.222:/oracle_oradata/expdp

5、无主键库导入

impdp mes_prd/mes800 directory=backup_mes dumpfile=exp_constraints-20230626.dmp logfile=exp_constraints-20230626_imp.log

6、找到报的错误进行处理

exp_constraints-20230626_imp