【工具准备】(版本仅为本人实际操作时使用,仅供参考)
DbVisualizer V6(提取informix数据)
PowerDesigner V12.5(informix数据文件向oracle转换)
PL.SQL.Developer.v7(向oracle中导数)
Oracle 10g(客户端即可)
UltraEdit(编辑informix数据文件中的非法格式及字符)
注:如果需要操作很大的文本文件,机器内存最好大一些,运行会比较顺畅。
【数据导出】
- 在DbVisualizer中建立informix连接(步骤从略)
- 数据库连接成功后,右单击“Data Base”,选择导出按钮,选择导出格式为“sql”,同时勾选下边的表结构、数据、索引、约束等;按需要我们分别导出一套含数据和一套不含数据的sql文件。
【数据库转换】
- 打开 PowerDesigner 12,File -> Reverse Engineer -> Database;
- DBMS选择INFORMIX SQL 9.x(版本根据informix数据库实际版本而定)然后点击【确定】按钮;
- 选择要导入的数据结构sql文件,点击【确认】按钮,这样我们从informix数据库中导出的数据结构便导入到PowerDesigner中了;
- 数据结构导入后,我们进行数据库变更操作 Datebase -> Change Current DBMS;
- 在new DBMS里选择oracle 10g,点击确定后,PowerDesigner会将informix中导出的数据结构文件转换成oracle可用的语法;
- 数据结构转换完成后,我们要生成oracle可用的执行sql,并生成相应脚本文件Database -> Generate Database;
- 在【General】标签输入选择生成脚本路径及脚本文件名;
- 在【Options】标签中将所有物理选项(Physical Options)勾选框去掉,这样在导出文件中指定表空间的语句就会自动被去掉;
- 生成建库sql,将【Preview】标签下sql复制出来,做成sql文件。
【完善数据结构sql】
(以下批量替换是指:利用UltraEdit编辑器,CTRL+R,并且勾选只匹配整个词语替换。)
- CHAR数据类型替换为VARCHAR2;
- SMALLINT数据类型替换为NUMBER(20);
- INTEGER数据类型替换为NUMBER(20);
- DATETIME数据类型替换为DATE;
- 由于“level”为oracle保留字,需要调整字段名,比如将level字段批量替换为****Level;
- Informix中自增序号字段转换之后会变成非法数值,通过正则表达式替换文档中非法数据类型,例如NUMBER(16,255)替换为NUMBER(16,2),搜索用到的正则表达式:”^(number(^)^(*^)^(,^)^(255)^)”;
- 建表sql中的“CONSTRAINT CHECK…”调整,举例如下:
CONSTRAINT CHECK((endhour >= 0 ),
constraint pk_** primary key (quotationno),
constraint CKT_*** check (r <= 24),
constraint Rule_11 check (24)
改为:
CONSTRAINT CKT_****_ENDHOUR CHECK((endhour >= 0 ),
constraint pk_**** primary key (quotationno)
- 核心数据结构中的dual表需要删除或者修改表名,因为dual为oracle系统表。
【在Oracle中导入数据结构及数据】
创建Oracle用户等操作从略。
- Informix导出的sql文件完善;
- 由于informix中的null与oracle中null的定义存在差异,在导入时,需要根据执行情况,将一些在数据结构中定义为非空的字段暂时设置为可空:
Informix中非空的定义为:notnull,表示空值('')或非空值(not '')
Oracle中非空的定义为:notnull,表示非空值(not '')
- 则有类似操作
alter table tablename modify columnname null;
[以下是个人实践中碰到问题的处理]
1.脚本整理
1.1结构替换
将类似 NUMBER(10,255) 替换为 NUMBER(10)
将varchar 替换为 varchar2
1.2数据替换
- 将类似TO_DATE('2010-04-07 09:41:59', '%Y-%m-%d %H:%M:%S'中的
%Y-%m-%d %H:%M:%S 替换为 YYYY-MM-DD HH24:MI:SS
%Y-%m-%d 替换为 YYYY-MM-DD
- 将 & 替换为 '||'&'||'
2.在库上执行注意点
2.1 禁用所有外键
执行此语句生成的脚本即可
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
2.2 执行脚本:如果数据量较大,建议在服务器上执行SQL脚本。
2.3 启用所有外键
执行此语句生成的脚本即可
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
3.部分数据处理
由于部分数据值中尾部有多余空格,需要做类似清理,下例是一般基础数据表的清理:
update tablename set columnname=replace(columnname,' ','') ;