最近碰到oracle数据库迁移,由于高级的迁移方法还不会,当前只会手动导入导出迁移。在迁移完所有数据表后,需要继续迁移索引、序列、视图、触发器、函数等。
本文主要说明批量迁移索引、序列、视图。触发器由于执行过程中异常,还没有找到方法自动处理;函数就只有几个手动处理掉了。
1. java程序
本次迁移面临问题:索引较多有68个,序列28个,视图8个。手动一个一个处理,看得眼睛都花了。就弄个java脚本处理。
不过各位碰到具体问题需要做微调。
package com.autonavi.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.sqlException;
import java.sql.sqlSyntaxErrorException;
import java.sql.Statement;
import com.autonavi.db.DataBaseConnection;
public class DealOracle {
private static DataBaseConnection cFactory = new DataBaseConnection();
static ResultSet rs = null;
static Connection connection1 = null;
static Connection connection2 = null;
static Statement statement = null;
public static void copyIndex(){
// 1.查询出建索引语句
String sql = "SELECT DBMS_MetaDATA.GET_DDL('INDEX',u.index_name) AS id FROM USER_INDEXES u";
try {
int count = 0;
PreparedStatement pState1 = null;
PreparedStatement pState2 = null;
pState1 = cFactory.createConnection4 ().prepareStatement(sql);
ResultSet rs = pState1.executeQuery();
while(rs.next()){
String str = rs.getString("ID");
String newstr = "";
if(str.contains("(DEGREE 0 INSTANCES 0)") == false){
count++;
String substr = str.substring(0,str.indexOf("TABLESPACE"));
newstr = substr+"TABLESPACE \"RUS\"";
//System.out.println("str:"+str);
System.out.println("newstr:"+newstr);
System.out.println("count:"+count);
try {
pState2 = cFactory.createConnection3().prepareStatement(newstr);
pState2.executeQuery(newstr);
pState2.close();
System.out.println("新建索引成功");
} catch (sqlSyntaxErrorException e) {
e.printStackTrace();
} catch (sqlException e) {
e.printStackTrace();
}
}
}
pState1.close();
} catch (sqlException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
cFactory.releaseConnection4();
cFactory.releaseConnection3();
}
}
public static void copySequence(){
// 1.查询出建序列语句
String sql = "SELECT DBMS_MetaDATA.GET_DDL('SEQUENCE',u.sequence_name) AS id FROM USER_SEQUENCES u";
try {
int count = 0;
PreparedStatement pState1 = null;
PreparedStatement pState2 = null;
pState1 = cFactory.createConnection4 ().prepareStatement(sql);
ResultSet rs = pState1.executeQuery();
while(rs.next()){
String str = rs.getString("ID");
count++;
System.out.println("sequence:"+str);
System.out.println("count:"+count);
try {
pState2 = cFactory.createConnection3().prepareStatement(str);
pState2.executeQuery(str);
pState2.close();
System.out.println("新建序列成功");
} catch (sqlSyntaxErrorException e) {
e.printStackTrace();
} catch (sqlException e) {
e.printStackTrace();
}
}
pState1.close();
} catch (sqlException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
cFactory.releaseConnection4();
cFactory.releaseConnection3();
}
}
public static void copyView(){
// 1.查询出建VIEW语句
String sql = "SELECT DBMS_MetaDATA.GET_DDL('VIEW',u.view_name) AS id FROM USER_VIEWS u";
try {
int count = 0;
PreparedStatement pState1 = null;
PreparedStatement pState2 = null;
pState1 = cFactory.createConnection4 ().prepareStatement(sql);
ResultSet rs = pState1.executeQuery();
while(rs.next()){
String str = rs.getString("ID");
count++;
System.out.println("view:"+str);
System.out.println("count:"+count);
try {
pState2 = cFactory.createConnection3().prepareStatement(str);
pState2.executeQuery(str);
pState2.close();
System.out.println("新建view成功");
} catch (sqlSyntaxErrorException e) {
e.printStackTrace();
} catch (sqlException e) {
e.printStackTrace();
}
}
pState1.close();
} catch (sqlException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
cFactory.releaseConnection4();
cFactory.releaseConnection3();
}
}
// 此方法有问题,不行。不过可以先通过下面的语句查询出触发器,手动创建。
public static void copyTriger(){
// 1.查询出建触发器语句
String sql = "SELECT DBMS_MetaDATA.GET_DDL('TRIGGER',u.trigger_name) AS id FROM USER_TRIGGERS u";
try {
int count = 0;
PreparedStatement pState1 = null;
PreparedStatement pState2 = null;
pState1 = cFactory.createConnection4 ().prepareStatement(sql);
ResultSet rs = pState1.executeQuery();
while(rs.next()){
String str = rs.getString("ID");
String newstr1 = "";
String newstr2 = "";
System.out.println("str:"+str);
newstr1 = str.substring(0,str.indexOf("ALTER"));
newstr2 = str.substring(str.indexOf("ALTER"));
count++;
System.out.println("trigger1:"+newstr1);
System.out.println("trigger2:"+newstr2);
System.out.println("count:"+count);
try {
pState2 = cFactory.createConnection3().prepareCall(newstr1);
pState2.executeUpdate();
//pState2 = cFactory.createConnection3().prepareStatement(newstr2);
//pState2.execute();
pState2.close();
System.out.println("新建触发器成功");
} catch (sqlSyntaxErrorException e) {
e.printStackTrace();
} catch (sqlException e) {
e.printStackTrace();
}
}
pState1.close();
} catch (sqlException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
cFactory.releaseConnection4();
cFactory.releaseConnection3();
}
}
public static void copyFunction(){
}
public static void main(String[] args){
copyView();
}
}
2. 处理结果
2.1. 创建索引
索引创建成功
如果之前已经有这个索引,提示重名。
2.2. 创建序列
序列创建成功
2.3. 创建视图
视图创建成功
3. 待解问题
java 处理触发器这块还没有弄清楚,望后续有时间了解下这方面的方法。