expdp 字符集从ZHS16GBK到AL32UTF8

源oracle数据库是GBK字符集，目标库是UTF8字符集，现在需要将源库的一个表空间数据导入到目标库。我的解决方法有点繁琐，首先直接导出源库的表空间

expdp trmuser/trmpass schemas=trmuser DIRECTORY=ORACLEDMP dumpfile=trmpass-$(date +%Y%m%d).dmp;

　　然后在目标库导入表空间

 impdp trmuser/trmpass remap_schema=trmuser:trmuser remap_tablespace=trm_data:trm_data DIRECTORY=ORACLEDMP dumpfile=trmpass-20190109.dmp table_exists_ac
tion=replace parallel=4 logfile=trmuser.log

　　导入后查看错误日志trmuser.log，然后执行程序，生成源库所要扩字段的SQL语句。

　　TableObject.java

package com.efounder.sdu.test;

public class TableObject {

    // 表名

    String tableName;

    // 字段名

    String columnName;

    // 原先最大值

    Integer oldMaxSize;

    // 现最大值

    Integer maxSize;

    Integer lineNum;

    // 修改字段的sql

    String sql;

    public Integer getLineNum() {

        return lineNum;

    }

    public void setLineNum(Integer lineNum) {

        this.lineNum = lineNum;

    }

    public String getSql() {

        return sql;

    }

    public void setSql(String sql) {

        this.sql = sql;

    }

    public String getTableName() {

        return tableName;

    }

    public void setTableName(String tableName) {

        this.tableName = tableName;

    }

    public String getColumnName() {

        return columnName;

    }

    public void setColumnName(String columnName) {

        this.columnName = columnName;

    }

    public Integer getMaxSize() {

        return maxSize;

    }

    public void setMaxSize(Integer maxSize) {

        this.maxSize = maxSize;

    }

    public Integer getOldMaxSize() {

        return oldMaxSize;

    }

    public void setOldMaxSize(Integer oldMaxSize) {

        this.oldMaxSize = oldMaxSize;

    }

}

　　CWZTTest4.java（根据自己用户表空间信息进行修改）

package com.efounder.sdu.test;

import java.io.*;

import java.text.SimpleDateFormat;

import java.util.*;

/**

 * todo

 *

 * @author wangxin

 * @version 2018/12/22 22:34

 */

public class CWZTTest4 {

    public static String SPACE = " ";

    public static void main(String[] args) throws Exception {

        String format = "yyyy-MM-dd HHmm";

        SimpleDateFormat sdf = new SimpleDateFormat(format);

        Long time = System.currentTimeMillis();

        String timeSuffix = "————" + sdf.format(new Date(Long.valueOf(time)));

        String localFilePath = "D:\\ChangeColumnLengthSql\\";

        // CHANGE ME-1

        String logName = "trm_impdp_20181223";

        String logFileName = localFilePath + logName + ".log";

        String outputFileName = localFilePath + logName + timeSuffix + ".txt";

        // CHANGE ME-2

        String oldUsername = "trmuser";

        // CHANGE ME-3

        String newUsername = "trmuser";

        String oldTablespaceName = "trm_data";

        String newTablespaceName = "trm_data";

        String newDirectory = "trm_dir";

        System.out.println(outputFileName);

        analyseLog(logFileName, outputFileName, oldUsername, newUsername, oldTablespaceName, newTablespaceName, newDirectory);

    }

    private static void analyseLog(String logFileName, String outputFileName, String oldUsername, String newUsername, String oldTablespaceName, String newTablespaceName, String newDirectory) throws Exception {

//        String path = "F:\\gateway\\gateway_impdp_20181225.log";

        String path = logFileName;

        File file = new File(path);

        InputStreamReader reader = new InputStreamReader(

                new FileInputStream(file));

        BufferedReader br = new BufferedReader(reader);

        String line = "";

        line = br.readLine();

        Map<String, TableObject> map = new LinkedHashMap<>();

        while (line != null) {

            String tableNameLine = "";

            String columnAndSizeLine = "";

            if (line.indexOf("ORA-02374") >= 0) {

                tableNameLine = line;

                line = br.readLine();

                columnAndSizeLine = line;

                String tableName = tableNameLine.split("\"")[3];

                String[] columnSize = columnAndSizeLine.split("ORA-12899: value too large for column |[ (]|maximum: |[)]");

                String columnName = columnSize[1];

                String oldMaxSzieStr = columnSize[6];

                Integer maxSize = Integer.parseInt(oldMaxSzieStr) * 2;

                String sql = "";

                sql += "ALTER TABLE " + tableName + " MODIFY (" + columnName + " VARCHAR2(" + maxSize + "));\r\n";

                sql += "UPDATE SYS_OBJCOLS SET COL_LEN = '" + maxSize + "' WHERE OBJ_ID = '" + tableName + "' AND COL_ID ='" + columnName + "';\r\n";

                sql += "\r\n";

                TableObject tableObject = new TableObject();

                tableObject.setTableName(tableName);

                tableObject.setColumnName(columnName);

                tableObject.setMaxSize(maxSize);

                tableObject.setSql(sql);

                map.put(tableName + "-" + columnName, tableObject);

            }

            line = br.readLine();

        }

        File outputFile = new File(outputFileName);

        OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(outputFile));

        BufferedWriter bufferedWriter = new BufferedWriter(writer);

        for (TableObject to : map.values()) {

            bufferedWriter.write(to.getSql());

        }

        bufferedWriter.close();

        Set<String> set = map.keySet();

        StringBuilder sb = new StringBuilder();

        StringBuilder sb2 = new StringBuilder();

        List<String> tableList = new ArrayList<>();

        for (String s : set) {

            String table = s.split("-")[0];

            tableList.add(table);

            sb.append(table);

            sb.append(",");

            sb2.append(oldUsername + "." + table);

            sb2.append(",");

        }

        String sqlStr = sb.toString();

        sqlStr = sqlStr.substring(0, sqlStr.length() - 1);

        String sqlStr2 = sb2.toString();

        sqlStr2 = sqlStr2.substring(0, sqlStr2.length() - 1);

        String expdp = "expdp " + oldUsername + "/zjtest TABLES=" +

                sqlStr +

                SPACE +

                "DIRECTORY=ORACLEDMP" +

                SPACE +

                "dumpfile=" + newUsername + "_uat_expdp_tableadd.dmp" +

                SPACE +

                "logfile=" + newUsername + "_expdp_tableadd.log";

        System.out.println(expdp + "\r\n");

        String impdp = "impdp " + newUsername + "/pansoft2019 TABLES=" +

                sqlStr2 +

                SPACE +

                "remap_schema=" + oldUsername + ":" + newUsername +

                SPACE +

                "remap_tablespace=" + oldTablespaceName + ":" + newTablespaceName +

                SPACE +

                "DIRECTORY=" + newDirectory + "" +

                SPACE +

                "dumpfile=" + newUsername + "_uat_expdp_tableadd.dmp" +

                SPACE +

                "table_exists_action=replace" +

                SPACE +

                "logfile=" + newUsername + "_expdp_tableadd.log";

        System.out.println(impdp);

    }

}

　　生成扩充字段SQL语句示例：

ALTER TABLE ZJ_JYMX MODIFY (F_FX VARCHAR2(4));

UPDATE SYS_OBJCOLS SET COL_LEN = '4' WHERE OBJ_ID = 'ZJ_JYMX' AND COL_ID ='F_FX';

ALTER TABLE ZJ_JYMX MODIFY (F_DFDW VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_JYMX' AND COL_ID ='F_DFDW';

ALTER TABLE ZJ_NBCDYWXX MODIFY (F_DFDW VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_NBCDYWXX' AND COL_ID ='F_DFDW';

ALTER TABLE ZJ_DBXX MODIFY (F_YHMC VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_DBXX' AND COL_ID ='F_YHMC';

ALTER TABLE ZJ_DBYWXX MODIFY (F_YHMC VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_DBYWXX' AND COL_ID ='F_YHMC';

ALTER TABLE ZJ_HSZHZL MODIFY (F_FKZY VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_HSZHZL' AND COL_ID ='F_FKZY';

ALTER TABLE ZJ_NBCDFDLL MODIFY (F_SJDW VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_NBCDFDLL' AND COL_ID ='F_SJDW';

ALTER TABLE ZJ_NBDCZQ MODIFY (F_SJDW VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_NBDCZQ' AND COL_ID ='F_SJDW';

ALTER TABLE ZJ_PJSQXX MODIFY (F_CPRZH_YH VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_PJSQXX' AND COL_ID ='F_CPRZH_YH';

ALTER TABLE ZJ_SXFL MODIFY (F_SXHT VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_SXFL' AND COL_ID ='F_SXHT';

ALTER TABLE ZJ_SXHTFS MODIFY (F_HTBH VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_SXHTFS' AND COL_ID ='F_HTBH';

ALTER TABLE ZJ_SXHTPZ MODIFY (F_HTBH VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_SXHTPZ' AND COL_ID ='F_HTBH';

ALTER TABLE ZJ_SXYWXX MODIFY (F_DWMC VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_SXYWXX' AND COL_ID ='F_DWMC';

ALTER TABLE ZJ_SXYWXX MODIFY (F_JGMC VARCHAR2(120));

UPDATE SYS_OBJCOLS SET COL_LEN = '120' WHERE OBJ_ID = 'ZJ_SXYWXX' AND COL_ID ='F_JGMC';

ALTER TABLE ZJ_TZHEAD MODIFY (F_XMMC VARCHAR2(100));

UPDATE SYS_OBJCOLS SET COL_LEN = '100' WHERE OBJ_ID = 'ZJ_TZHEAD' AND COL_ID ='F_XMMC';

ALTER TABLE ZJ_BHSQXX MODIFY (F_KZYHFHMC VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_BHSQXX' AND COL_ID ='F_KZYHFHMC';

ALTER TABLE ZJ_BHSQXX MODIFY (F_SYRMC VARCHAR2(80));

UPDATE SYS_OBJCOLS SET COL_LEN = '80' WHERE OBJ_ID = 'ZJ_BHSQXX' AND COL_ID ='F_SYRMC';

ALTER TABLE ZJ_FKZLB MODIFY (F_FK_ZHMC VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_FKZLB' AND COL_ID ='F_FK_ZHMC';

ALTER TABLE BIZ_MONITOR MODIFY (F_LXMC VARCHAR2(120));

UPDATE SYS_OBJCOLS SET COL_LEN = '120' WHERE OBJ_ID = 'BIZ_MONITOR' AND COL_ID ='F_LXMC';

ALTER TABLE MDM_ITEM MODIFY (F_CHAR6 VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'MDM_ITEM' AND COL_ID ='F_CHAR6';

ALTER TABLE ZJ_BHYWXX MODIFY (F_SQSY VARCHAR2(2000));

UPDATE SYS_OBJCOLS SET COL_LEN = '2000' WHERE OBJ_ID = 'ZJ_BHYWXX' AND COL_ID ='F_SQSY';

ALTER TABLE ZJ_JNJSXX MODIFY (F_FK_JGMC VARCHAR2(120));

UPDATE SYS_OBJCOLS SET COL_LEN = '120' WHERE OBJ_ID = 'ZJ_JNJSXX' AND COL_ID ='F_FK_JGMC';

ALTER TABLE ZJ_NBCDXX MODIFY (F_SJDW VARCHAR2(60));

UPDATE SYS_OBJCOLS SET COL_LEN = '60' WHERE OBJ_ID = 'ZJ_NBCDXX' AND COL_ID ='F_SJDW';

　　然后在源库执行SQL语句进行扩充字段

  sqlplus trmuser/trmpass@10.20.31.201:1521/orcl

　　在源库扩充完字段后重新导出数据，然后我选择最直接的方法目标库直接重建表空间然后重新导入，字符不一致问题解决。

巴特西

expdp 字符集从ZHS16GBK到AL32UTF8

最新文章

热门文章