expdp 字符集从ZHS16GBK到AL32UTF8

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

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

　　然后在目标库导入表空间

1	`impdp trmuser/trmpass remap_schema=trmuser:trmuser remap_tablespace=trm_data:trm_data DIRECTORY=ORACLEDMP dumpfile=trmpass-20190109.dmp table_exists_ac<br>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（根据自己用户表空间信息进行修改）

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

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';