最新版本:Oracle Database 11g ,硬件要求比较高,选择Oracle 10g Express做实验。
Oracle Express安装
http://www.oracle.com/technetwork/database/express-edition/downloads/102xewinsoft-090667.html
服务介绍 :
必须启动OracleXETNListener,OracleServiceXE。
一.第三方工具
找了比较多,比较好的
1.Intelligent Converters
大部分可以转换,效果不理想,有局限性
http://convert-in.com/mss2ora.htm
Features
* Convert individual tables
* Convert indexes with all necessary attributes
* Convert foreign keys
Limitations
* Does not convert views
* Does not convert stored procedures and triggers
优点:可转换部分表。
缺点:不支持视图,存储过程,触发器,数据导入(5条),类型对比。2005年的软件,更新很少。
2.Power Design的正反向工程
过程曲折,效果不理想,也只能转换部分表。
二.自带工具
1.通过 sqlserver management studio 导出
无很好的链接组件
2. Oracle SQL Developer
介绍: http://www.oracle.com/technetwork/developer-tools/sql-developer/what-is-sqldev-093866.html
特点:free graphical tool,migrating 3rd party databases,Debug,Unit Testing...
下载:http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
优点:官方工具,可以转换很多元素。
缺点:没有数据导入
三.自己开发
不要只看到结果,要知道是怎么做的,弄清楚来龙去脉。
转换元素:
表,视图,存储过程,索引等,主要做表的转换。
Oracle和sqlserver转换需要注意的:
数据类型的不同:
Guid,oracle默认和sqlserver不一样,需要转换
自增,oracle通过SEQUENCE 序列实现
oracle 统一大写,要区别大小写加双引号,如: "TableName"
表的转换:表列表->单个表结构(主键 ,外键,索引,数据类型)->对比->oracle的表
一个简单的sqlserver到oracle数据类型转换策略:
{
string re = string .Empty;
switch (type)
{
case " uniqueidentifier " :
re = " CHAR(36 CHAR) " ;
break ;
case " char " :
re = string .Format( " CHAR({0} CHAR) " , int .Parse(length) > 4000 ? " 4000 " : length);
break ;
case " nchar " :
re = string .Format( " NCHAR({0} CHAR) " , int .Parse(length) > 4000 ? " 4000 " : length);
break ;
case " varchar " :
if (length != " -1 " )
re = string .Format( " VARCHAR2({0} CHAR) " , int .Parse(length) > 4000 ? " 4000 " : length);
else
re = " CLOB " ;
break ;
case " nvarchar " :
if (length != " -1 " )
re = string .Format( " NVARCHAR2({0}) " , int .Parse(length) > 4000 ? " 4000 " : length); // 不指定CHAR
else
re = " NCLOB " ; // NCLOB会截断
break ;
case " text " :
case " xml " :
re = " CLOB " ;
break ;
case " image " :
re = " BLOB " ;
break ;
case " ntext " :
re = " NCLOB " ; // NCLOB会截断
break ;
case " int " :
case " integer " :
re = " NUMBER(10,0) " ;
break ;
case " smallint " :
re = " NUMBER(5,0) " ;
break ;
case " money " :
re = " NUMBER(19,4) " ;
break ;
case " decimal " :
re = string .Format( " NUMBER({0},{1}) " , length, scale_len);
break ;
case " real " :
re = " FLOAT(24) " ;
break ;
case " bigint " :
re = " NUMBER(19,0) " ;
break ;
case " bit " :
re = " NUMBER(1,0) " ;
break ;
case " tinyint " :
re = " NUMBER(3,0) " ;
break ;
case " date " :
case " datetime " :
case " smalldatetime " :
re = " DATE " ;
break ;
case " binary " :
re = " RAW " ;
break ;
case " varbinary " :
if (length != " -1 " )
re = " RAW " ;
else
re = " BLOB " ;
break ;
default :
re = string .Format( " {0}({1}) " , type, length);
break ;
}
return re;
}
数据的转换:小数据->sql语句
大数据->程序导入 (大数据截断)
数据的导入可根据Sqlserver数据库的表结构拼接sql语句,分析其字段的数据类型,通过OracleParameter的方式导入到Oracle的表中。
{
DataTable dt = PersonalDB.Query( string .Format( " select * from {0} " , table));
List < string > cs = new List < string > ();
for ( int i = 0 ; i < dt.Columns.Count; i ++ )
{
string c = dt.Columns[i].ColumnName;
cs.Add(c);
}
string sets = string .Empty;
string values = string .Empty;
for ( int i = 0 ; i < cs.Count; i ++ )
{
sets += string .Format( " \"{0}\" " , cs[i]);
values += string .Format( " :{0} " , cs[i]);
if (i < cs.Count - 1 )
{
sets += " , " ;
values += " , " ;
}
}
string sql = string .Format( " BEGIN insert into \"{0}\" ({1}) values ({2}); END; " , table, sets, values);
string connectionString = ConfigurationManager.ConnectionStrings[ " oracle " ].ConnectionString;
OracleConnection connection = new OracleConnection(connectionString);
connection.Open();
for ( int i = 0 ; i < dt.Rows.Count; i ++ )
{
DataRow row = dt.Rows[i];
OracleCommand cmd = new OracleCommand(sql, connection);
cmd.CommandType = CommandType.Text;
foreach ( string c in cs)
{
OracleParameter op = new OracleParameter();
op.ParameterName = c;
cmd.Parameters.Add(op);
}
cmd.ExecuteNonQuery();
}
connection.Close();
}
扩展: 视图,存储过程,函数等转换。
接口,支持多数据库
可配置的数据类型转换
支持海量数据:控制台 或者 bs程序
参考:
Oracle® Database SQL Reference 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm