最近在将项目由SQL SERVER 2008向ORACLE 11G迁移,以下简述迁移数据表过程。
- 产生数据库的脚本。在脚本生成向导中选择脚本选项中,设置ANSI填充FALSE、编写USE DATABASE脚本FALSE、编写扩展属性脚本FALSE、架构限定对象名称FALSE,为服务器版本编写脚本选择SQL SERVER 2008
- 生成到新文件或到新窗口后保存到文件,然后使用支持正则表达式的文本编辑器打开(建议使用EmEditor,否则可能下面某些正则表达式会稍有不同),使用查找替换功能依次如下操作(以下如未说明则都为使用正则表达式查找):
- 由于ORACLE的名称限定符是引号而非方括号,因此要将[和]替换成空字符串以去除。假如有使用关键字作为名称的,需要再单独处理了
查找:\[|\]
替换为:空字符串 - 去除SET ANSI_NULLS ON和SET QUOTED_IDENTIFIER ON,因为ORACLE中不存在这些属性或不是这个名称,使用正则可以很方便的完成这个工作:
查找:SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n
替换为:空字符串。 - 替换所有的GO为;同样使用正则表达式来做:
查找:\nGO\n
替换为:;\n - 注释掉IDENTITY(1,1),因为ORACLE中不支持这个关键字,相应的,为了实现自增数字的功能,需要建立一个SEQUENCE,然后在表上建立一个INSERT触发器,触发时往对应字段赋值为代替。后面我会写一个PYTHON脚本,根据这些注释生成所有的SEQUENCE和触发器脚本。
查找:(IDENTITY\(\d+,\d+\))
替换为:/*\1*/ - 去除所有的“ ON PRIMARY”,“ TEXTIMAGE_ON PRIMARY”
查找:TEXTIMAGE_ON PRIMARY|ON PRIMARY
替换为:空字符串 - 去除所有的“ CLUSTERED ”、“ NONCLUSTERED ”、“WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)”
查找:((NON)*CLUSTERED)|(WITH \(PAD[^\)]+\))
替换为:空字符串 - 主键语句中,去除ASC和DESC,注意别误伤视图语句中的相应字符串。
查找:( ASC| DESC) 注意字符前的空格,以免误伤标识符中的对应字符
替换为:空字符串 - 替换字段类型。因为SQL SERVER中的类型和ORACLE中大部分都不一样,因此基本上都需要替换掉。具体替换时建议按如下步骤(注意如果在标识符中没有关键字,那么在替换时勾选全字匹配即可,如果可能存在使用关键字当标识符的时候,那么,查找时使用正则表达式,在查找字符串前加上[ ]^1,表示查找字符串前存在并只存在1个空格,如原类型后必然有括号的,例如char/varchar之类,那么在查找字符串后加上\(,必然没有括号的加上[ ]^1,然后在替换字符串中根据前后是否有空格或括号,要依样加上,如int替换成number(10,0),使用正则时,查找字符串内填[ ]^1int[ ]^1,替换字符串内填 NUMBER(10,0) ,注意前后的空格,再如varchar替换成varchar2时,查找字符串填[ ]^1varhcar\(,替换字符串写 varchar2(,注意前面的空格和后面的括号。):
- varchar替换成varchar2
- nvarchar也是替换成varchar2,但是长度要乘2,所以这个要单独每种长度都独立替换
- 同样要乘2的还包括所有n开头的类型
- int替换成NUMBER(10,0)
- NUMERIC和DECIMAL替换成NUMBER
- smalldatetime和datetime替换成DATE
- text替换成CLOB
- image替换成BLOB
- 其它类型对应表如下:
SQL SERVER ORACLE 数字类型 DECIMAL[(P[,S])] NUMBER[(P[,S])] NUMERIC[(P[,S])] NUMBER[(P[,S])] FLOAT[(N)] NUMBER[(N)] 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)] NCHAR[(N)] CHAR[(N*2)] NVARCHAR[(N)] VARCHAR2[(N*2)] 日期时间类型 DATETIME DATE SMALLDATETIME DATE 其它 TEXT CLOB IMAGE BLOB BIT NUMBER(1) UNIQUEIDENTIFIER LONG RAW
- 替换默认值规则:
- 替换默认日期:
查找(普通非正则):(getdate())
替换为:SYSDATE - 数字默认值语法替换,把数字外的括号去除:
查找:( DEFAULT )\((\d+)\)
替换为:\1\2 - 替换默认值语法:
查找:ALTER TABLE (\w+) ADD\s+CONSTRAINT.+DEFAULT (.+) FOR (\w+);
替换为:alter table \1 modify \3 default \2;
- 替换默认日期:
- 去除外键语句中一些不支持的选项(在我的数据库中碰到的。应该不完整),以及,SQL SERVER生成的约束有时会先使用WITH NOCHECK创建约束后紧跟着将之启用,虽然可以用ORACLE对应的DISALBE和ENABLE选项替代,但感觉没有什么用,于是干脆把WITH NOCHECK去掉,同时却掉紧跟着的启用约束语句。具体如下:
查找:ON UPDATE CASCADE\n|\nNOT FOR REPLICATION|ALTER TABLE.+CHECK CONSTRAINT.+\n|WITH NOCHECK|WITH CHECK
替换为:空字符串
- 由于ORACLE的名称限定符是引号而非方括号,因此要将[和]替换成空字符串以去除。假如有使用关键字作为名称的,需要再单独处理了
- 转换的过程中,应注意以下问题:
- 标识符长度不能超过30(包括表、约束、视图等等所有的标识标)
- varchar2的长度不能超过4000
- 好的,保存修改后的SQL脚本文件,现在可以给ORACLE执行了吗?等等,还有。。我们要生成IDENTITY的替代脚本!下面是PYTHON脚本代码,把它保存为文本文件,例如c:\convert.py中,然后,在CMD中执行
c:\>%path_to_python.exe% c:\convert.py [sql原文件路径] [生成的触发器和seqence的sql文件保存路径]
不过,这个脚本只支持一个表中存在一个IDENTITY字段的情况。:(#coding=utf-8
import sys
import re
#sys.setdefaultencoding('utf8')
orgfile=sys.argv[1]
newfile=sys.argv[2]
f=open(orgfile,'r')
fidentity=open(newfile,"w")line=f.read()
p=re.compile('CREATE TABLE ([^\(\n]+)[^;]+?(\S+) NUMBER\(10,0\)/\* IDENTITY\((\d+),(\d+)\)\*/',re.I|re.S|re.M)
mat=p.findall(line)
for item in mat:
seqname="SEQ_"+item[0]
triggername="TRI_"+item[0]+"_"+item[1]if len(seqname)>30:
seqname=seqname[0:29]
if len(triggername)>30:
triggername=triggername[0:29]
createseq="create sequence "+seqname+" increment by "+item[3]+" start with "+item[2]+";\n"
createseq=createseq+"CREATE OR REPLACE TRIGGER " + triggername +"\n\
BEFORE INSERT ON "+item[0]+"\n\
FOR EACH ROW\n\
WHEN (new."+item[1]+" IS NULL)\n\
BEGIN\n\
Select "+seqname+".NEXTVAL INTO :NEW."+item[1]+"\n\
FROM DUAL;\n\
END;\n/\n"
fidentity.write(createseq)
fidentity.flush()
fidentity.close()
f.close() - 最后,依次执行两个SQL脚本文件。
- 如果有错,那么真是太糟糕啦。根据错误提示,排错吧。 :(