Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1017579
  • 博文数量: 171
  • 博客积分: 55
  • 博客等级: 民兵
  • 技术积分: 2077
  • 用 户 组: 普通用户
  • 注册时间: 2012-01-04 10:11
个人简介

pugna

文章分类

全部博文(171)

文章存档

2021年(4)

2020年(1)

2019年(4)

2018年(5)

2017年(7)

2016年(9)

2015年(36)

2014年(8)

2013年(96)

2012年(1)

分类: Oracle

2014-11-04 23:47:44


最近在使用impdp导入数据的时候,因源库gbk与目标库utf8的字符集不一样,造成导入时目标库中表的字段长度不够。报错信息如下:

点击(此处)折叠或打开

  1. ORA-02374: conversion error loading table "HN_CM_IRMS_35_TNMS"."PTN_NE_TO_SERVICE"
  2. ORA-12899: value too large for column TP_FDN (actual: 202, maximum: 200)
  3. ORA-02372: data for row: TP_FDN : 0X'454D533D414E2D5433312D322D503A4D616E61676564456C65'
  4.  
  5. ORA-02374: conversion error loading table "HN_CM_IRMS_35_TNMS"."PTN_NE_TO_SERVICE"
  6. ORA-12899: value too large for column TP_FDN (actual: 202, maximum: 200)
  7. ORA-02372: data for row: TP_FDN : 0X'454D533D414E2D5433312D322D503A4D616E61676564456C65'
  8.  
  9. ORA-02374: conversion error loading table "HN_CM_IRMS_35_TNMS"."PTN_NE_TO_SERVICE"
  10. ORA-12899: value too large for column TP_FDN (actual: 202, maximum: 200)
  11. ORA-02372: data for row: TP_FDN : 0X'454D533D414E2D5433312D322D503A4D616E61676564456C65'
  12.  
  13. ORA-02374: conversion error loading table "HN_CM_IRMS_35_TNMS"."PTN_NE_TO_SERVICE"
  14. ORA-12899: value too large for column TP_FDN (actual: 208, maximum: 200)
  15. ORA-02372: data for row: TP_FDN : 0X'454D533D414E2D5433312D322D503A4D616E61676564456C65'

这个日志有150M,260W行。手动筛选信息肯定是不行的。

写了一个根据impdp导入日志信息中字段长度不够的错误信息自动生成修改表结构SQL的python3脚本

点击(此处)折叠或打开

  1. #!/usr/bin/python3
  2. # -*- coding: utf-8 -*-

  3. import os;
  4. import sys;
  5. import codecs;
  6. import re;

  7. def replace_file():
  8.     try:
  9.         input_filepath = '/home/oracle/impdp.log';
  10.         with codecs.open(input_filepath,'r','gb18030') as source_file:
  11.             source_lines = source_file.readlines();
  12.         output_filepath = '/home/oracle/mid_result.txt';
  13.         list_str = [];
  14.         tempstr = '';
  15.         for line in source_lines:
  16.             if line.startswith("ORA-02374"):
  17.                 tempstr = line.strip();
  18.             elif line.startswith("ORA-12899"):
  19.                 tempstr = tempstr + line.strip() + '\n';
  20.                 list_str.append(tempstr);
  21.             else:
  22.                 pass;
  23.         with codecs.open(output_filepath,'w+','utf-8') as output_file:
  24.             write_list(output_file,list_str);
  25.         print("Finish replace the file !");
  26.     except Exception as error:
  27.         print(error);
  28.         pass;

  29. def create_sql():
  30.     try:
  31.         input_filepath = '/home/oracle/mid_result.txt';
  32.         with codecs.open(input_filepath,'r','utf-8') as source_file:
  33.             source_lines = source_file.readlines();
  34.         output_filepath = '/home/oracle/result.txt';
  35.         list_str = [];
  36.         set_table = set();
  37.         for line in source_lines:
  38.             target_len = int(re.sub(r'.*maximum:\s(\d+)\)',r'\1',line)) * 2;
  39.             mid_sql = line.replace('ORA-02374: conversion error loading table','ALTER TABLE').replace('ORA-12899: value too large for column',' MODIFY');
  40.             #list_str.append(line.replace('ORA-02374: conversion error loading table','ALTER TABLE').replace('ORA-12899: value too large for column',' MODIFY'));
  41.             set_table.add(re.sub(r'\(.*\)','varchar2(' + str(target_len) + ');',mid_sql));
  42.         with codecs.open(output_filepath,'w+','utf-8') as output_file:
  43.             write_set(output_file,set_table);
  44.         print("Finish generate sql !");
  45.     except Exception as error:
  46.         print(error);
  47.         pass;

  48. def write_str(p_file, p_content):
  49.     try:
  50.         if isinstance(p_content, str):
  51.             p_file.write(p_content);
  52.         else:
  53.             print("it is not str");
  54.     except Exception as error:
  55.         print(error);
  56.         pass;

  57. def write_list(p_file, p_content):
  58.     try:
  59.         if isinstance(p_content, list):
  60.             for item in p_content:
  61.                 if isinstance(item, str):
  62.                     write_str(p_file, item);
  63.                 else:
  64.                     print("Too complicated")
  65.         else:
  66.             print("it is not list");
  67.     except Exception as error:
  68.         print(error);
  69.         pass;


  70. def write_set(p_file, p_content):
  71.     try:
  72.         if isinstance(p_content, set):
  73.             for item in p_content:
  74.                 if isinstance(item, str):
  75.                     write_str(p_file, item);
  76.                 else:
  77.                     print("Too complicated")
  78.         else:
  79.             print("it is not set");
  80.     except Exception as error:
  81.         print(error);
  82.         pass;

  83. if __name__== '__main__':
  84.     replace_file();
  85.     create_sql();

最终生成的/home/oracle/result.txt就是用于修改表结构的SQL语句。

阅读(4229) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~