SQL SERVER 2008向ORACLE 11G迁移示例

来源于：http://www.cnblogs.com/hiizsk/

由SQL SERVER 2008向ORACLE 11G迁移过程记录之一-表

使用Oracle Sql Developer将SQL SERVER 2008数据库移植到Oracle 11g（一）

使用Oracle Sql Developer将SQL SERVER 2008数据库移植到Oracle 11g（二）

使用Oracle Sql Developer将SQL SERVER 2008数据库移植到Oracle 11g（三）

使用Oracle Sql Developer将SQL SERVER 2008数据库移植到Oracle 11g（四）

使用Oracle Sql Developer将SQL SERVER 2008数据库移植到Oracle 11g（五）

 

最近在将项目由SQL SERVER 2008向ORACLE 11G迁移，以下简述迁移数据表过程。

1. 产生数据库的脚本。在脚本生成向导中选择脚本选项中，设置ANSI填充FALSE、编写USE DATABASE脚本FALSE、编写扩展属性脚本FALSE、架构限定对象名称FALSE，为服务器版本编写脚本选择SQL SERVER 2008
2. 生成到新文件或到新窗口后保存到文件，然后使用支持正则表达式的文本编辑器打开（建议使用EmEditor，否则可能下面某些正则表达式会稍有不同），使用查找替换功能依次如下操作（以下如未说明则都为使用正则表达式查找）：
   1. 由于ORACLE的名称限定符是引号而非方括号，因此要将[和]替换成空字符串以去除。假如有使用关键字作为名称的，需要再单独处理了 
      查找：\[|\] 
      替换为：空字符串
   2. 去除SET ANSI_NULLS ON和SET QUOTED_IDENTIFIER ON，因为ORACLE中不存在这些属性或不是这个名称，使用正则可以很方便的完成这个工作： 
      查找：SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n 
      替换为：空字符串。
   3. 替换所有的GO为;同样使用正则表达式来做： 
      查找：\nGO\n 
      替换为：;\n
   4. 注释掉IDENTITY(1,1)，因为ORACLE中不支持这个关键字，相应的，为了实现自增数字的功能，需要建立一个SEQUENCE，然后在表上建立一个INSERT触发器，触发时往对应字段赋值为代替。后面我会写一个PYTHON脚本，根据这些注释生成所有的SEQUENCE和触发器脚本。 
      查找：(IDENTITY\(\d+,\d+\)) 
      替换为：/*\1*/
   5. 去除所有的“ ON PRIMARY”，“ TEXTIMAGE_ON PRIMARY” 
      查找：TEXTIMAGE_ON PRIMARY|ON PRIMARY 
      替换为：空字符串
   6. 去除所有的“ 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[^\)]+\)) 
      替换为：空字符串
   7. 主键语句中，去除ASC和DESC，注意别误伤视图语句中的相应字符串。 
      查找：( ASC| DESC)  注意字符前的空格，以免误伤标识符中的对应字符 
      替换为：空字符串
   8. 替换字段类型。因为SQL SERVER中的类型和ORACLE中大部分都不一样，因此基本上都需要替换掉。具体替换时建议按如下步骤(注意如果在标识符中没有关键字，那么在替换时勾选全字匹配即可，如果可能存在使用关键字当标识符的时候，那么，查找时使用正则表达式，在查找字符串前加上[ ]^1，表示查找字符串前存在并只存在1个空格，如原类型后必然有括号的，例如char/varchar之类，那么在查找字符串后加上\(，必然没有括号的加上[ ]^1，然后在替换字符串中根据前后是否有空格或括号，要依样加上，如int替换成number(10,0)，使用正则时，查找字符串内填[ ]^1int[ ]^1，替换字符串内填 NUMBER(10,0) ，注意前后的空格，再如varchar替换成varchar2时，查找字符串填[ ]^1varhcar\(，替换字符串写 varchar2(，注意前面的空格和后面的括号。)：
      1. varchar替换成varchar2
      2. nvarchar也是替换成varchar2，但是长度要乘2，所以这个要单独每种长度都独立替换
      3. 同样要乘2的还包括所有n开头的类型
      4. int替换成NUMBER(10,0)
      5. NUMERIC和DECIMAL替换成NUMBER
      6. smalldatetime和datetime替换成DATE
      7. text替换成CLOB
      8. image替换成BLOB
      9. 其它类型对应表如下：

         	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

   9. 替换默认值规则：
      1. 替换默认日期： 
         查找（普通非正则）：(getdate()) 
         替换为：SYSDATE
      2. 数字默认值语法替换，把数字外的括号去除： 
         查找：( DEFAULT )\((\d+)\) 
         替换为：\1\2
      3. 替换默认值语法： 
         查找：ALTER TABLE (\w+) ADD\s+CONSTRAINT.+DEFAULT (.+) FOR (\w+); 
         替换为：alter table \1 modify \3 default \2;
   10. 去除外键语句中一些不支持的选项（在我的数据库中碰到的。应该不完整），以及，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 
       替换为：空字符串
3. 转换的过程中，应注意以下问题：
   1. 标识符长度不能超过30（包括表、约束、视图等等所有的标识标）
   2. varchar2的长度不能超过4000
4. 好的，保存修改后的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()

5. 最后，依次执行两个SQL脚本文件。
6. 如果有错，那么真是太糟糕啦。根据错误提示，排错吧。 ：（