一、MES数据库问题描述
1、现象
（1）开发描述，mes_prd用户下的很多表的列属性变为了4000。
（2）所有表的列数量
2、分析原因
在早期，数据库曾中毒，后期数据导入后，没有再进行检查，最近测试才发现。
二、解决方案测试
使用expdp/impdp导入导出功能进行测试。
1、需要的数据和库
（1）需要的数据
有问题的生产库A的数据导出（expdp）-192.168.20.219
正常的生产库B的数据结构导出（expdp）-192.168.20.220
（2）需要的数据库库 -192.168.20.222
测试库C：用于导入生产库B的数据结构和生产库A的数据。
2、导出生产库A的数据-192.168.20.219
expdp mes_prd/mes800 directory=BACKUP_PRD dumpfile=lyyw20230711-expdp.dmp logfile=expdp_metadata2.log content=METADATA_ONLY exclude=statistics
3、导出生产库B的数据结构-192.168.20.220
expdp mes_prd/mes800 directory=BACKUP_PRD dumpfile=expdp_metdata.dmp logfile=expdp_metadata.log content=METADATA_ONLY exclude=statistics
4、测试库C的操作-192.168.20.222
（1）创建用户和表空间等
drop user mes_prd cascade; drop tablespace mes_prd including conntents and datafiles; drop tablespace MES_PRD_TEMP_NEW including contents and datafiles; drop tablespace MES_MM including contents and datafiles; drop tablespace MES_MM_TEMP including contents and datafiles; create user mes_prd identified by mes800; grant dba to mes_prd; grant read,write on directory backup_mes to mes_prd; create tablespace MES_PRD datafile '/oradata/oradata/MES_PRD_DATA.dbf' size 4000M autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; ALTER TABLESPACE MES_PRD ADD DATAFILE '/oradata/oradata/MES_PRD_DATA01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; ALTER TABLESPACE MES_PRD ADD DATAFILE '/oradata/oradata/MES_PRD_DATA02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; ALTER TABLESPACE MES_PRD ADD DATAFILE '/oradata/oradata/MES_PRD_DATA03.dbf' SIZE 1000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; ALTER TABLESPACE MES_PRD ADD DATAFILE '/oradata/oradata/MES_PRD_DATA04.dbf' SIZE 1000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; ALTER TABLESPACE MES_PRD ADD DATAFILE '/oradata/oradata/MES_PRD_DATA05.dbf' SIZE 1000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; ALTER TABLESPACE MES_PRD ADD DATAFILE '/oradata/oradata/MES_PRD_DATA06.dbf' SIZE 1000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; create temporary tablespace MES_PRD_TEMP_NEW TEMPFILE '/oradata/oradata/MES_PRD_TEMP_NEW.dbf' size 1000M reuse autoextend on next 200M maxsize unlimited; create user MES_PRD identified by **** default tablespace MES_PRD temporary tablespace MES_PRD_TEMP_NEW; grant connect,dba,resource to MES_PRD ; create or replace directory backup_PRD as '/oradata/expdp'; GRANT READ,WRITE ON DIRECTORY backup_PRD to MES_PRD; GRANT READ,WRITE ON DIRECTORY backup_PRD to system; create tablespace MES_MM datafile '/oradata/oradata/MES_MM_DATA.dbf' size 4000M autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; create temporary tablespace MES_MM_TEMP TEMPFILE '/oradata/oradata/MES_MM_TEMP.dbf' size 500M reuse autoextend on next 20M maxsize unlimited;
（2）导入生产库B的数据结构
impdp mes_prd/mes800 directory=BACKUP_PRD dumpfile=expdp_metdata.dmp logfile=expdp_metadata.log exclude=statistics
（3）导入生产库数据库A的数据
impdp mes_prd/mes800 directory=BACKUP_PRD dumpfile=MESPRD_20230711001000.DMP logfile=lyyw20230715.log TABLE_EXISTS_ACTION=append exclude=statistics
三、根据告警，处理相关数据
1、处理超出字段列长度的表
（1）从日志中获取表信息
KUP-11007: conversion error loading table "MES_PRD"."MES_DRAWING_PIC" ORA-12899: value too large for column PICNAME (actual: 60, maximum: 50)
KUP-11007: conversion error loading table "MES_PRD"."MES_BAN_STATUS" ORA-12899: value too large for column BUFFER79 (actual: 51, maximum: 50) KUP-11007: conversion error loading table "MES_PRD"."MES_SO_MAIN_YW" ORA-12899: value too large for column BUFFER30 (actual: 54, maximum: 50)
KUP-11007: conversion error loading table "MES_PRD"."MES_QM_COMPLAIN" ORA-12899: value too large for column BUFFER7 (actual: 53, maximum: 50) KUP-11009: data for row: BUFFER7 : 0X'514996FE5EA6FF1A539F68370036002053CD99886837003100'
（2）删除上述表，并获取表中数据最大的长度
（3）导入原悲愤中此表数据
（4）修改表的列长
2、处理有值为null的
（1）获取有null值的表
KUP-11007: conversion error loading table "MES_PRD"."NEIP_LOG" KUP-11007: conversion error loading table "MES_PRD"."SYSSTB" KUP-11007: conversion error loading table "MES_PRD"."MES_JMT_SEND" KUP-11007: conversion error loading table "MES_PRD"."PRDIFO" KUP-11007: conversion error loading table "MES_PRD"."MES_JMT_DELIVERSHEETS" KUP-11007: conversion error loading table "MES_PRD"."MES_JMT_QMDEAL" KUP-11007: conversion error loading table "MES_PRD"."HRCQA" KUP-11007: conversion error loading table "MES_PRD"."MES_SO_OTHERMAIN" KUP-11007: conversion error loading table "MES_PRD"."OO_USERFUNC" KUP-11007: conversion error loading table "MES_PRD"."NEIP_RELATIONSHIPSETLOG" KUP-11007: conversion error loading table "MES_PRD"."SA_PERSALLO"
（2）然后呢?
想想。
3、处理其他问题
ORA-31693: Table data object "MES_PRD"."MES_SO_MAIN_YW" failed to load/unload and is being skipped due to error: ORA-00001: unique constraint (MES_PRD.UNIQUE_MES_SO_MAIN_YW) violated
ORA-31693: Table data object "MES_PRD"."MES_SO_APPRESULT" failed to load/unload and is being skipped due to error: ORA-00001: unique constraint (MES_PRD.PK_MES_SO_APPRESULT) violated
ORA-31693: Table data object "MES_PRD"."MES_WC_PROPER" failed to load/unload and is being skipped due to error: ORA-00001: unique constraint (MES_PRD.PK_MES_WC_PROPER) violated
ORA-31693: Table data object "MES_PRD"."MES_SO_OTHERDETAIL" failed to load/unload and is being skipped due to error: ORA-00001: unique constraint (MES_PRD.PK_MES_SO_OTHERDETAIL) violated
