用Java实现SQL Server到Oracle数据迁移.docx

用Java实现SQL Server到Oracle数据迁移.docx

《用Java实现SQL Server到Oracle数据迁移.docx》由会员分享，可在线阅读，更多相关《用Java实现SQL Server到Oracle数据迁移.docx（35页珍藏版）》请在冰豆网上搜索。

用Java实现SQLServer到Oracle数据迁移

用JAVA实现SQLServer到Oracle的数据迁移

中国科学院西安网络中心陈拓

2005年9月25日

以下的操作以SQLServer2000和Oracle10g为例。

开发工具使用JDveloper10.1.3。

一、在Oracle数据库中建立一个新帐户

在“用SQLDTS实现SQLServer到Oracle的数据迁移”一文中我们介绍过怎样创建一个新帐户，如果已有可用用户，跳过这一部分。

1．用SQL*plus创建新用户

●建立一个新帐户ct，PL/SQL脚本如下：

SETECHOOFF

PROMPT

PROMPTspecifypasswordforctasparameter1（ct）:

DEFINEpass=&1

PROMPT

PROMPTspecifydefaulttablespeaceforctasparameter2（USERS）:

DEFINEtbs=&2

PROMPT

PROMPTspecifytemporarytablespaceforCTasparameter3（TEMP）:

DEFINEttbs=&3

PROMPT

PROMPTspecifypasswordforSYSasparameter4（sys）:

DEFINEpass_sys=&4

PROMPT

PROMPTspecifylogpathasparameter5（D:

\oracle\product\10.1.0\）:

DEFINElog_path=&5

PROMPT

--Thefirstdotinthespoolcommandbelowis

--theSQL*Plusconcatenationcharacter

DEFINEspool_file=&log_path.ct_main.log

SPOOL&spool_file

REM=======================================================

REM删除用户及其方案

REM=======================================================

DROPUSERCTCASCADE;

REM=======================================================

REM创建新用户，指定默认表空间和临时表空间

REM=======================================================

CREATEUSERCTIDENTIFIEDBY&pass;

ALTERUSERCTDEFAULTTABLESPACE&tbs

QUOTAUNLIMITEDON&tbs;

ALTERUSERCTTEMPORARYTABLESPACE&ttbs;

REM=======================================================

REM授权CONNCETANDRESOURCE角色

REM=======================================================

GRANTCONNECTTOCT;

GRANTRESOURCETOCT;

REM=======================================================

REM从sys方案授权（方案\SYS\源类型\程序包\dbms_stats）

REM=======================================================

CONNECTsys/&pass_sysASSYSDBA;

GRANTexecuteONsys.dbms_statsTOct;

REM=======================================================

REM设置本次会话的语言、区域和字符集

REM=======================================================

CONNECTct/&pass

ALTERSESSIONSETNLS_LANGUAGE='SIMPLIFIEDCHINESE';

ALTERSESSIONSETNLS_TERRITORY=CHINA;

ALTERSESSIONSETNLS_CHARSET=ZHS16GBK;

REM=======================================================

REM解锁

REM=======================================================

SQL>alteruserhridentifiedbyhraccountunlock;

spooloff

●查看相关信息

用DBA_ROLES视图查看角色信息：

SQL>connsys/sys@oractassysdba

SQL>select*fromDBA_ROLES;

用DBA_ROLE_PRIVS

视图查看授予授予用户的角色：

SQL>select*fromDBA_ROLE_PRIVSwhereGRANTEE='CT';

用ROLE_SYS_PRIVS视图查看授予角色的系统权限：

SQL>select*fromROLE_SYS_PRIVSwhereROLE='CONNECT';

SQL>select*fromROLE_SYS_PRIVSwhereROLE='RESOURCE';

用SESSION_ROLES

视图查看用户当前已启用的角色：

SQL>connct/ct@oract

SQL>select*fromSESSION_ROLES;

●将此脚本存为文件ct_main.sql

●以SYS或SYSTEM登录

C:

\>sqlplussys/sysassysdba

●运行脚本ct_main.sql

SQL>@D:

\lecture\oracle\ct_main.sql

●查看操作日志文件：

D:

\oracle\product\10.1.0\ct_main.log

2．用JDeveloper创建新用户

你也可以用JDeveloper创建新用户。

用于创建一个连接的用户名必须有足够的权限来创建用户，该用户既可以被授予CREATEUSER权限，也可以被授予一个象管理员那样包含该权限的角色。

1）选择View|ConnectionNavigator。

2）展开Database并且选择一个databaseconnection。

3）在connection中，右击schema（与用户名相同）或者其中的任一项，选择New。

4）从NewGallery窗口的FilterBy组合框中选择AvailableItems。

5）从NewGallery窗口的Categories树型目录中展开DatabaseTier并且选择DatabaseObjects。

6）从Items窗口选择User，并且单击确定打开CreateDatabaseUser窗口，见图1。

图1NewGallery窗口

7）在“创建数据库用户”窗口中输入用户名和口令参数，并选择默认表空间和临时表空间选项，见图2。

图2创建数据库用户窗口

8）单击确定创建新用户。

二、安装MicrosoftSQLServer数据库JDBC驱动程序

1．下载JDBC驱动程序

●从下面的微软网站下载JDBC驱动程序，支持JDK1.3。

执行setup.exe安装SQLServerJDBC驱动程序。

在安装目录下有帮助文件。

●下载SQLServer2000DriverforJDBCServicePack3，支持JDK1.4。

执行setup.exe安装SQLServerJDBC驱动程序。

在安装目录下有帮助文件。

2．设置驱动程序库Libraries

•Tools->ManageLibraries…打开ManageLibraries窗口，如图3。

图3ManageLibraries窗口

•在“管理库”窗口中选择Libraries标签。

•选择User。

•单击下面的New按钮，打开CreateLibrary窗口，见图4。

图4CreateLibrary窗口

•在“创建库”窗口中设置LibraryName为SQLServerJDBC。

•选择ClassPath:

，单击AddEntry…按钮，打开SelectPathEntry对话框，图5。

图5SelectPathEntry窗口

•在“选择路径入口”窗口中选择安装的驱动程序路径下的lib文件夹中的文件msbase.jar、mssqlserver.jar和msutil.jar，单击Select回到CreateLibrary窗口，单击确定回到ManageLibraries窗口，单击确定。

三、测试SQLServerJDBC驱动程序

1．创建一个到SQLServer的新连接

1）选择View|ConnectionNavigator

2）右击Database并且选择一个Newdatabaseconnection…，打开创建数据库连接向导，见图6。

图6创建数据库连接向导1/4步

3）在“创建数据库连接向导1/4步”的ConnectionName文本框中输入SQLServer_conn；Connectiontype，选择ThirdPartyJDBCDriver，下一步，见图7。

图7创建数据库连接向导2/4步

4）在“创建数据库连接向导2/4步”的窗口中输入Username：

sa；Password：

***，单击下一步，图8。

图8创建数据库连接向导3/4步

5）在“创建数据库连接向导3/4步”的窗口中单击New…按钮打开RegisterJDBCDriver对话框，见图9。

图9RegisterJDBCDriver对话框

6）注册JDBC驱动

●在DriverClass文本中输入：

com.microsoft.jdbc.sqlserver.SQLServerDriver。

●单击Browse按钮，找到前面设置的SQLServerJDBC驱动程序库，单击确定回到创建数据库连接向导3/4步，见图8。

7）在URL文本中输入：

jdbc:

microsoft:

sqlserver:

//:

1433;SelectMethod=cursor;DatabaseName=

在这里，为localhost；为ckgl。

单击下一步，见图10。

图10创建数据库连接向导4/4步

8）在“创建数据库连接向导4/4步”的窗口中单击TestConnection按钮进行测试,单击下一步，完成。

在“连接导航”窗口中展开database节点，再展开SQLServer_conn连接，在dbo文件夹中查看数据库对象。

2．用JAVA程序测试

●在JDeveloper10.1.3中导入SQLServerJDBC驱动程序：

⏹右击要使用SQLServerJDBC的项目。

⏹选择ProjectProperties…，打开项目属性窗口，图11。

图11项目属性窗口

⏹在ProjectProperties窗口左侧选择Libraries，在右侧单击AddLibrary…按钮打开AddLibrary窗口，图12。

图12项目属性窗口

⏹在AddLibraries窗口中找到前面设置的SQLServerJSBC驱动程序库，单击确定。

回到ProjectProperties窗口，图13。

图13AddLibraries窗口

⏹单击确定。

●重复上面的步骤添加OracleJDBC驱动程序。

图14添加OracleJDBC驱动程序

●在JDeveloper10.1.3中写连接SQLServer的JDBCJAVA测试程序：

⏹用JDeveloper添加一个新的JavaClass，命名为SQLServerTest.java，测试程序代码：

packagemypackage;

publicclassSQLServerTest{

privatejava.sql.Connectioncon=null;

privatefinalStringurl="jdbc:

microsoft:

sqlserver:

//";

privatefinalStringserverName="localhost";

privatefinalStringportNumber="1433";

privatefinalStringdatabaseName="pubs";

privatefinalStringuserName="sa";

privatefinalStringpassword="****";

privatefinalStringselectMethod="cursor";

publicSQLServerTest（）{

}

privatejava.sql.ConnectiongetConnection（）{

try{

//A.注册SQLServerJDBC驱动程序

Class.forName（"com.microsoft.jdbc.sqlserver.SQLServerDriver"）;

//B.创建新数据库连接

con=java.sql.DriverManager.getConnection（getConnectionUrl（）,userName,password）;

if（con!

=null）System.out.println（"ConnectionSuccessful!

"）;

}catch（Exceptione）{

e.printStackTrace（）;

System.out.println（"ErrorTraceingetConnection（）:

"+e.getMessage（））;

}

returncon;

}

privateStringgetConnectionUrl（）{

returnurl+serverName+":

"+portNumber+";databaseName="+databaseName+";selectMethod="+

selectMethod+";";

}

privatevoidcloseConnection（）{

try{

if（con!

=null）

con.close（）;

con=null;

}catch（Exceptione）{

e.printStackTrace（）;

}

}

publicvoiddisplayDbProperties（）{

java.sql.DatabaseMetaDatadm=null;

java.sql.ResultSetrs=null;

try{

con=this.getConnection（）;

if（con!

=null）{

dm=con.getMetaData（）;

System.out.println（"驱动器信息：

"）;

System.out.println（"\t驱动器名字:

"+dm.getDriverName（））;

System.out.println（"\t驱动器版本:

"+dm.getDriverVersion（））;

System.out.println（"\n数据库信息：

"）;

System.out.println（"\t数据库名字:

"+dm.getDatabaseProductName（））;

System.out.println（"\t数据库版本:

"+dm.getDatabaseProductVersion（））;

System.out.println（"显示可用的数据库目录：

"）;

rs=dm.getCatalogs（）;

while（rs.next（））{

System.out.println（"\tcatalog:

"+rs.getString

（1））;

}

rs.close（）;

rs=null;

closeConnection（）;

}elseSystem.out.println（"Error:

NoactiveConnection"）;

}catch（Exceptione）{

e.printStackTrace（）;

}

dm=null;

}

publicstaticvoidmain（String[]args）{

SQLServerTestsQLServerTest=newSQLServerTest（）;

sQLServerTest.displayDbProperties（）;

}

}

⏹编译运行，结果如下

图15SQLServerJDBC连接测试程序运行结果

●在JDeveloper10.1.3中写连接Oracle的JDBCJAVA测试程序：

⏹用JDeveloper添加一个新的JavaClass，命名为OracleTest.java，测试程序代码：

packagemypackage;

importjava.sql.Connection;

importjava.sql.DatabaseMetaData;

importjava.sql.DriverManager;

importjava.sql.SQLException;

publicclassOracleTest{

publicOracleTest（）{

}

publicstaticvoidmain（String[]args）throwsSQLException{

//A.注册OracleJDBC驱动程序

DriverManager.registerDriver（neworacle.jdbc.driver.OracleDriver（））;

//B.创新新数据库连接

Connectionconn=DriverManager.getConnection（"jdbc:

oracle:

thin:

@localhost:

1521:

oract","ct","ct"）;

//C.取得连接数据

DatabaseMetaDatamd=conn.getMetaData（）;

System.out.println（"数据库版本：

"）;

System.out.println（"------------------------------------------------"）;

System.out.println（md.getDatabaseProductVersion（））;

System.out.println（）;

System.out.println（"驱动程序名称与版本：

"）;

System.out.println（"------------------------------------------------"）;

System.out.print（md.getDriverName（）+""+md.getDriverVersion（））;

//D.关闭数据库连接

conn.close（）;

}

}

⏹编译运行，结果如下

图16Oracle测试程序运行结果

四、SQLServer和Oracle数据类型的对应关系

1．SQLSERVER与ORACLE的数据类型不同

当数据从SQLSERVER2000向ORACLE10g迁移时，可以做如下调整：

SQLSERVER

ORACLE

数字类型

DECIMAL[（P[,S]）]

NUMBER[（P[,S]）]

NUMERIC[（P[,S]）]

NUMBER[（P[,S]）]

FLOAT[（N）]

NUMBER[（N）]

INT

NUMBER

SMALLINT

NUMBER

TINYINT

NUMBER

MONEY

NUMBER[19,4]

SMALLMONEY

NUMBER[19,4]

字符类型

CHAR[（N）]

CHAR[（N）]

VARCHAR[（N）]

VARCHAR2[（N）]

日期时间类型

DATETIME

TIMESTAMP（6）

SMALLDATETIME

TIMESTAMP（6）

其它

TEXT

CLOB

IMAGE

BLOB

BIT

NUMBER

（1）

2．ID列向SEQUENCE迁移

在SQLSERVER中，可以将数据库中的某一字段定义为IDENTITY列以做主键识别，如：

ItemIDintidentity（1,1）/*记录编号字段*/

在这里，ItemID是一个ID列，在向具有该列的表插入记录时，系统将从1开始以1的步长自动对ItemID的值进行维护。

但在ORACLE中，没有这样的ID列定义，而是采用另一种方法，即创建SEQUENCE。

例如，创建SEQUENCESEQ_ITEMID用于表Portal_Discussion的ITEMID列：

createsequenceSEQ_DISC_ITEMIDincrementby1/*该SEQUENCE以1为步长递增*/

startwith1maxvalue99999;/*从1开始，最大增长到99999*/

实际操作时引用SEQUENCE的下一个值，例如：

insertintoPortal_Discussion（ITEMID,MODULEID,TITLE）