上周接到一个任务,公司的测试环境需要从MySQL移植到Oracle上,Oracle上已经有一些相应的表、字段了,因此只需要去转换剩下的表和数据传输了。网上找了很多方案,也动手过很多次,其中也遇到了很多坑,记录一下。
-
Oracle SQL Developer
这个是Oracle公司的一个产品,怎么说呢?这个工具可能是我不是太会用,用最新版工具进行数据库迁移的时候总是包各种稀奇古怪的错误,网上的解决方案也少,最后还是根据网上的建议,将版本降为4.0.0.12.84,然后就一次性的移植成功了,移植的步骤网上有很多,我就不一一例举出来了,这里说下这个软件的一些坑。
在用这个软件进行数据库移植的过程中,是需要关联迁移资料数据库
如果你在这步操作上失败了,有可能是这个用户没被授予DBA权限,没有权限的话创建一些表是会失败的。
还有一个比较坑的是,MySQL数据库移植到Oracle上,是根据MySQL上面的数据库名来创建用户,如果你事先没有创建好相应的用户,也没有给用户赋值相应的表空间,那到时候移植的时候就会有问题。
首先它会先创建一个用户(名字跟数据库名一样),然后所有数据都会移植到USERS表空间下,我之前就是这样,然后需要转表空间,很麻烦,后来换了种相对于我来说,比较简单的方法。 -
Navicat Premium+Kettle+JDBC
使用这种方式,相对于我来说,可能比较便捷,因为要转换的表不是很多。
首先,使用Navicat Premium12进行结构转换,这里选择数据传输
然后选择文件形式,当然这里也可以直接选择连接,进行数据库之间的数据迁移,不过出错的机率比较大,这里保守一点,选择文件,选择好你需要转换的数据库跟SQL格式和文件输出地址,我这边输出的是SQL文件,选择完成之后先不要点击下一步,先点击左上角的选项
这三点需要注意一下,首先创建记录这个可以去掉,去掉这个就是只建立表结构,没有任何数据,当然你也可以勾选,勾选了的话表中的数据也会导入到相应的SQL文件里面去,遇到错误继续 跟创建前删除对象 选项的话,看个人喜好。
还有一个就是,有时候MySQL表结构转换的话也会出问题,比如Bit类型转换有时候就会出问题,我的处理方式是将带有bit字段的表先复制一份,然后再改字段类型。
然后在点击下一步。
点击开始
表结构转换完成
然后再使用工具把SQL文件导入到相应的Oracle上就行。至此,表结构转换好了。
接下来就是kettle了,这个工具的下载,配置网上有很多教程,需要注意的是需要将数据库连接放到工具的lib目录下,在这里,MySQL用的是mysql-connector-java-5.1.42.jar,Oracle用的是ojdbc5.jar、ojdbc6.jar。配置完成后双击Spoon.bat,这里如果启动失败,可能是JDK的环境变量没配好。
选择文件–>新建–>转换
点击测试,如果报Error connecting to database: (using class oracle.jdbc.driver.OracleDriver),这种错误一般都是数据库名称填写错误,此时需要登陆数据库,查询一下数据库名
select INSTANCE_NAME from v$instance;
配置好两个数据库连接后,在核心对象里面选择输入将表输入拖动到旁边的转换框中,表输出也是如此,然后点击表输入,按住shift键移动到表输出上,这样两个表之间就建立起了连接。
双击表输入,选择好数据库连接,点击获取SQL查询语句,选择相应的表,点击确定
点击是
点击确认,表输入就配好了,然后双击表输出,选择好数据库连接,目标模式(选择好数据库),目标表,选择好相应的数据库表,然后点击获取字段
选择清除并增加所有
确定,然后选择运行,安全模式要勾选,点击启动
运行完成后,如果成功不要理会,如果失败,查看失败信息,如果出现异常ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值,我这里是用JDBC处理这种表。下面是JDBC代码
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//数据库连接类
public class AllJdbc {
public static Connection getOpen() {
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver" );
conn=DriverManager.getConnection(
"jdbc:mysql://127.0.0.1/test","test","test"
);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static Connection getOOpen() {
Connection conn=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver" );
conn=DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:orcl","test","test"
);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn) throws Exception{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
System.out.println(e);
}
}
}
}
package test;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.google.common.collect.Maps;
public class Main {
public static void main(String[] args) throws Exception {
AllJdbc my = new AllJdbc ();
Connection conn = my.getOpen();
String tableName = " 表名";
String sql = "select * from " + tableName;
PreparedStatement preStat = null;
try {
preStat = conn.prepareStatement(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//获得查询字段
ResultSet rs = preStat.executeQuery();
Map<String,String> metaDataMap = getMetaData(rs);
String fileName = "";
String paramData = "";
for(Map.Entry<String, String> entity : metaDataMap.entrySet()) {
fileName += ","+entity.getKey();
paramData += ",?";
}
fileName = fileName.replaceFirst(",", "");
paramData = paramData.replaceFirst(",", "");
String sqls = " insert into " + tableName + "("+ fileName +") values (" + paramData + ")";
//打印SQL
System.out.println(sqls);
Connection conns = my.getOOpen();
int s = 0;
while(rs.next()) {
//关闭自动提交
conns.setAutoCommit(false);
PreparedStatement preStats = null;
try {
preStats = conns.prepareStatement(sqls);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
int i = 1;
for(Map.Entry<String, String> entity : metaDataMap.entrySet()) {
preStats.setObject(i++, rs.getObject(entity.getKey()));
}
//打印第一个字段值,出问题了好找地方
System.out.println(rs.getObject(1));
System.out.println(preStats.executeUpdate());
conns.commit();
s++;
}
//打印数据总数
System.out.println(s);
conn.close();
conns.close();
}
//将字段转换,存进map里面
public static Map<String,String> getMetaData(ResultSet rs){
Map<String,String> metaDataMap = Maps.newHashMap();
int colomn;
try {
ResultSetMetaData rsmd = rs.getMetaData();
colomn = rsmd.getColumnCount();
for(int i = 1 ; i <= colomn ;i++) {
metaDataMap.put(rsmd.getColumnName(i), rsmd.getColumnTypeName(i));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return metaDataMap;
}
}
两个类,可能不是很完善,不过够用,而且数据量比较大的话,需要修改Oracle的游标,具体我就不贴出来了,至此转换便完成了?。