我去年接了一个迁移项目一套跑了好几年的SQL Server系统要整体搬到Oracle原因很现实集团统一数据库平台新项目和周边系统全都走Oracle老系统只能跟着迁。当时我觉得这活儿不复杂导出、导入、改改连接串也就两三天的事可真做起来才知道SQL Server到Oracle的数据库迁移是一连串连环坑从表结构到函数、从工具到权限每一层都有让你挠头的地方。这篇东西就是那次迁移的完整复盘我会按实际推进的顺序把思路和代码写出来适合正在做迁移、准备做迁移或者只想知道两边到底差在哪的人参考。1. 迁移前的盘点比“导数据”更重要的准备工作1.1 列清数据库资产清单表、对象、依赖一个不能漏迁移不是把表导过去就完事我第一周做的最有价值的事就是把源库里的“资产”全部列清楚。除了业务表还有视图、存储过程、函数、触发器、作业、用户权限、自定义类型。我用下面这几条SQL把SQL Server的元数据摸了一遍-- 所有用户表 SELECT s.name AS schema_name, t.name AS table_name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id ORDER BY s.name, t.name; -- 所有存储过程和函数 SELECT o.name, o.type_desc FROM sys.objects o WHERE o.type IN (P, FN, IF, TF) ORDER BY o.name; -- 所有视图和触发器 SELECT o.name, o.type_desc FROM sys.objects o WHERE o.type IN (V, TR) ORDER BY o.name;这一步看着简单却能直接决定后续排期。我手里这个系统有200多张表、40多个存储过程、十几个作业还有一些老旧的DTS包工具链完全是另一套。建议你把依赖关系也理一理哪些表被报表系统读取哪些表和外部系统做接口哪些存储过程跑在夜间作业里。迁移前如果漏了定时作业上线后数据对不上排查会非常麻烦。1.2 Oracle版本选型11g还是19c不是越新越好网上搜“Oracle 11g下载资源”“oracle安装教程11g”的人特别多因为很多存量Oracle系统还在用11g。但如果你是从零开始搭目标库我更推荐直接用19c或21c理由很简单11g太老官方扩展支持已经结束很多新硬件和操作系统上装起来问题不断安全补丁也跟不上。当然现实中不一定能由你说了算。有些企业为了兼容旧组件或者采购合同的关系目标库已经定死是11g那也建议你搞清楚具体是11.2.0.4还是更早的版本因为11.2.0.4是11g里最稳定的补丁版本很多坑已经被磨平了。安装时注意几点安装前检查系统内存、swap、/tmp空间Oracle对共享内存有硬性要求。安装过程建议选择“仅安装数据库软件”之后再用DBCA建库便于控制字符集和数据库名称。字符集一定要在创建数据库时定好推荐AL32UTF8。如果建库后才发现需要改字符集成本会非常高。如果身边有人问“用哪个图形工具连Oracle”我的默认答案是SQL Developer对于DBA够用Navicat对于迁移阶段更顺手因为它能同时连SQL Server和Oracle同一界面里搬数据很方便。1.3 工具链选型SSMA、Navicat、SQL Developer怎么选微软官方其实有一个免费的SQL Server Migration Assistant for OracleSSMA很多人不知道。它的优势在于能自动做大量类型映射和语法转换尤其是存储过程、函数这类对象能帮你把T-SQL改成PL/SQL的初稿。但注意自动转换不等于能用复杂业务逻辑必须人工复查。我的实际组合是这个用SSMA做结构迁移和初步SQL转换用Navicat做数据搬运用SQL Developer做日常查询、版本对比和验证。三种工具各有分工别指望一个工具解决所有问题。特别是SSMA生成的PL/SQL代码我在多个存储过程里发现它把SQL Server的GETDATE()转成SYSTIMESTAMP但某些场景下业务需要的是不带时区的SYSDATE这种语义差异机器是猜不透的。2. 表结构迁移类型映射、自增列与默认值的克制处理2.1 字段类型映射表照抄会出问题的地方表结构迁移最常见的问题是类型强行对等。不同数据库的存储模型不一样直接照抄会在数据量上来后暴露性能问题。我整理了一份核心映射表迁移时基本对照着用SQL ServerOracle说明intNUMBER(10)常用整型bigintNUMBER(19)大整型smallintNUMBER(5)小整型tinyintNUMBER(3)0~255decimal(p,s)NUMBER(p,s)定点数保留精度floatBINARY_DOUBLE浮点注意精度差异bitNUMBER(1)逻辑值0/1char / ncharCHAR / NCHAR定长字符varchar / nvarcharVARCHAR2 / NVARCHAR2变长字符长度单位要确认text / ntextCLOB大文本imageBLOB二进制大对象datetimeDATEOracle的DATE已包含时分秒datetime2TIMESTAMP更高精度的日期时间uniqueidentifierRAW(16)建议用RAW存GUID性能更好moneyNUMBER(14,2)金额类型xmlXMLTYPEXML数据这里有个隐藏坑是VARCHAR2的长度。SQL Server的nvarchar(100)按字符数算Oracle里的VARCHAR2(100)默认按字节算如果数据库字符集是AL32UTF8一个中文会占3个字节这样原来能存100个汉字的字段迁移后只能存33个。所以字符字段迁移时要人为把长度放大或者显式使用VARCHAR2(100 CHAR)。2.2 字符串转数字隐式转换和NLS格式的双重陷阱热词里“sqlserver 字符串转数字”搜的人很多因为两边转换语法确实不一样。SQL Server用CAST或CONVERTSELECT CAST(123 AS INT); SELECT CONVERT(DECIMAL(10,2), 123.45);Oracle用TO_NUMBERSELECT TO_NUMBER(123) FROM dual; SELECT TO_NUMBER(123,45, 999D99, NLS_NUMERIC_CHARACTERS,.) FROM dual;第二个例子就是重点了。Oracle的TO_NUMBER很依赖会话的NLS参数尤其是NLS_NUMERIC_CHARACTERS它定义了小数点和千分位分隔符。默认如果是英文系统小数点是.千分位是,但如果源系统是中文或欧洲环境可能会出现反过来的情况直接TO_NUMBER(1,234.56)会报ORA-01722: invalid number。我的建议是所有涉及字符串转数字的SQL都显式指定格式掩码不要依赖环境默认值。否则同一套脚本在不同客户端工具里执行结果可能不一样这种问题排查起来非常隐蔽。2.3 IDENTITY自增列从IDENTITY到SEQUENCE的改造SQL Server的自增列用IDENTITY(1,1)Oracle 11g没有这个语法通常用序列加触发器实现。如果你的Oracle是12c及以上可以直接用Identity列CREATE TABLE user_info ( user_id NUMBER GENERATED BY DEFAULT AS IDENTITY, user_name VARCHAR2(100) );但如果是11g就得老老实实创建序列和触发器CREATE SEQUENCE seq_user_id START WITH 1 INCREMENT BY 1; CREATE OR REPLACE TRIGGER trg_user_id_before_insert BEFORE INSERT ON user_info FOR EACH ROW WHEN (NEW.user_id IS NULL) BEGIN SELECT seq_user_id.NEXTVAL INTO :NEW.user_id FROM dual; END;这里需要特别小心如果应用层已经在业务逻辑里通过某种方式生成主键再加触发器会导致主键冲突。迁移前要跟开发确认主键生成到底在哪一层。我在项目里就遇到过一张表既有触发器又在应用里写了自定义ID生成逻辑上线高峰时报主键重复最后是把应用逻辑拿掉只留触发器才解决。3. 数据搬迁的实操路径从Navicat到批量脚本的选择3.1 Navicat跨库传输最快但最需要小心的方法数据量不大的情况下Navicat的数据传输功能很省事。它能同时配置SQL Server源和Oracle目标直接表对表导。但用之前有三个地方必须确认目标表已经建好并且字段映射正确大字段CLOB/BLOB在源库查询时不要被截断两边会话的编码一致尤其是中文数据。在Navicat里点“数据传输”选择表后可以先“预览”查看映射关系不要直接执行。我之前导过一次源库表的datetime字段被默认映射成了TIMESTAMP(7)虽然也能用但后续JDBC驱动读出来带小数秒后应用解析报错来回折腾了大半天。现在的习惯是每张表都单独生成结构脚本审核完再导数据。3.2 手动生成INSERT脚本的适用场景如果只是几张配置表手动生成INSERT脚本就够了。在SQL Server里用bcp导出或者直接用工具生成INSERT INTO ... VALUES。但要注意Oracle对单条SQL的长度和绑定变量数量有限制如果你生成的是几千行的超长INSERT脚本执行时会非常慢而且容易爆undo表空间。我遇到的情况是需要把十几张基础配置表迁过去每张几百行。我的做法是让开发写SQL把源表数据拼成Oracle风格的INSERT ALL语句或者分批生成单个INSERT每500行提交一次。这样出错了能快速定位是哪一批的数据有问题不用整张表重跑。3.3 大批量导入的性能优化提交策略和并行度数据量超过百万行时上述方式都太慢这时候就要上批量导入工具。Oracle的SQL*Loader是首选它支持直接文本加载比逐条INSERT快很多。还有一种方式是在SQL Server里通过链接服务器写到Oracle但配置复杂不如SQL*Loader干净。SQL Server端有一个容易被忽略的写法是WITH (TABLOCKX)它能够在批量操作时减少锁竞争。比如从SQL Server导出数据时SELECT * FROM dbo.large_table WITH (TABLOCKX)这样做的目的是在导出阶段拿表级锁避免其他写操作干扰并发也能让源库的读一致性更好。但千万别在业务高峰期这么干否则会阻塞线上写请求。导入到Oracle时建议分批提交每批500到1000行不要一次性攒十万行再提交否则一旦遇到约束错误回滚代价很大。我习惯的做法是先TRUNCATE目标表再按主键或时间范围分片导入每片导入后立刻统计行数跟源库同口径对比。4. SQL语句改造T-SQL与PL/SQL的语法分水岭4.1 分页查询OFFSET FETCH 和 ROWNUM 的写法差异分页是应用改造里最容易被发现的差异。SQL Server从2012开始支持OFFSET ... FETCHOracle从12c开始也支持同样的写法所以如果你目标库是12c分页基本可以无痛迁移SELECT * FROM user_info ORDER BY user_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;但如果目标库是11g上面这段直接报错必须改用ROWNUM的三层嵌套写法SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT * FROM user_info ORDER BY user_id ) t WHERE ROWNUM 30 ) WHERE rn 20;注意Oracle对ROWNUM的处理是“先取出再排序”所以一定要把“排序后的结果”先包一层子查询再限制行数否则分页结果会乱掉。这个写法看起来很繁琐但性能上有保障特别是配合索引排序。4.2 字符串处理、CASE WHEN 和关于DUAL的冷知识字符串拼接两边差异很明显。SQL Server用SELECT ID: CAST(user_id AS VARCHAR(10)) FROM user_info;Oracle用||SELECT ID: || TO_CHAR(user_id) FROM user_info;很多人都知道这个区别但实际改造时容易漏掉的是GETDATE()。SQL Server里GETDATE()是当前时间Oracle里对应的是SYSDATE但如果你需要带毫秒的时间应该是SYSTIMESTAMP。我见过有人把GETDATE()无脑替换成CURRENT_TIMESTAMP在两边都能执行但返回类型在JDBC驱动里不一样可能引发日期解析问题。CASE WHEN的写法两边几乎一致基本不用改。更多人在意的是Oracle的DUAL表。问题“oracle中dual最多存多大”听起来有点无厘头但实际反映了一个常见误区DUAL是Oracle里用来执行SELECT常量表达式的虚拟表它不需要也不应该被当成真实业务表来存数据。任何时候你在Oracle里写SELECT TO_CHAR(SYSDATE,YYYY-MM-DD) FROM dual;它都只返回一行。别想着往dual里塞数据那是Oracle保留的内部对象。4.3 存储过程迁移变量、游标、异常处理的差异存储过程迁移是迁移工作量的大头。语法层面最核心的差异是PL/SQL使用:赋值而T-SQL用SET或SELECT。举一个简单的例子SQL Server写法DECLARE cnt INT; SET cnt 1; SELECT cnt COUNT(*) FROM dbo.user_info;Oracle写法DECLARE v_cnt NUMBER; BEGIN v_cnt : 1; SELECT COUNT(*) INTO v_cnt FROM user_info; END;注意Oracle的SELECT INTO要求查询结果必须恰好一行如果返回多行会报TOO_MANY_ROWS没有返回会报NO_DATA_FOUND。SQL Server没有这个限制所以迁移时要在存储过程里加异常处理BEGIN SELECT COUNT(*) INTO v_count FROM order_header WHERE cust_id v_cust_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_count : 0; END;游标使用上Oracle通常用FOR循环隐式游标比显式游标干净很多FOR rec IN (SELECT id, name FROM user_info WHERE status A) LOOP -- 处理每一行 END LOOP;T-SQL里写惯了DECLARE cursor_name CURSOR FOR ...的人刚开始会不太适应但只要习惯这个写法代码量会少很多。5. 真实踩坑记录监听失败、ORA-28547、重复数据清理5.1 Oracle监听服务无法启动的五步排查链路好多人在装完Oracle后卡在“Oracle监听服务无法启动”热词榜上这个词常年在。我第一次也遇到过后来总结了一条排查链路遇到基本按顺序走用lsnrctl status看当前监听状态如果报TNS-12541: TNS:no listener说明监听服务没起来。用lsnrctl start启动监听观察输出。如果卡住或直接报错大概率是listener.ora配置有问题。检查$ORACLE_HOME/network/log/listener.log里的错误日志这里能看到具体原因比如端口被占用、地址绑定失败。用netstat -ano | findstr :1521确认1521端口是否被占用。有时候是本机别的程序抢占了端口。检查listener.ora中的HOST配置如果写的localhost这种没问题的但如果你写的是一台已经不存在的机器名监听也起不来。改成IP或者任意网络接口能解决。如果你的Windows服务列表里显示Oracle监听服务启动了但Navicat依然连不上那八成是服务名/SID和实际不匹配而不是监听本身有问题。5.2 ORA-28547连接错误Navicat连接时最容易踩的雷错误ORA-28547: connection to server failed, probable Oracle Net admin error我第一次看到时一脸懵。大致意思就是Oracle Net层连接失败。Navicat连接Oracle时最容易触发这个错误的原因是连接类型选了“基本”之后服务名填错了。Oracle 11g默认数据库实例名是orcl但很多人误填成SQL Server那种“数据库名”比如填TestDB或者把SQL Developer里的服务名和SID搞混了。正确的Navicat连接配置是连接类型基本主机localhost或IP端口1521服务名orcl或实际的服务名不是SID如果你在服务器上用DBCA建的库叫orcl服务名就是orcl不是ORCL.EXAMPLE.COM那种全局数据库名也不是Windows主机名。用tnsping orcl能通基本连接就稳了。这个问题排查起来不难但很耽误时间建议迁移前先把目标连接串在Navicat里压测一遍。5.3 无主键表删除重复数据只保留一条的两种写法热词里“sqlserver删除重复数据只保留一条 无id”是另一个高频问题。SQL Server在没有唯一ID的情况下通常用ROW_NUMBER()窗口函数。比如对user_info表按name, mobile去重;WITH cte AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY name, mobile ORDER BY (SELECT 0)) AS rn FROM dbo.user_info ) DELETE FROM cte WHERE rn 1;Oracle也有ROW_NUMBER()但更经典的写法是直接用ROWID。由于Oracle每行都有物理地址ROWID可以这样删DELETE FROM user_info WHERE rowid NOT IN ( SELECT MIN(rowid) FROM user_info GROUP BY name, mobile );这种写法在Oracle里干净利落。迁移到Oracle后如果遇到这种清理需求优先用ROWID方案因为可读性好、性能也不错。但要注意如果表上没有索引GROUP BY全表扫描是跑不掉的数据量大时先建个临时索引再操作。5.4 数据校验与使用DG备库当“对照实验场”数据搬过去不代表迁移结束最怕的是搬过去之后才发现差数据。我的校验方式分三层行数对比每张表源库和目标库SELECT COUNT(*)做差汇总对比金额类字段做SUM对比日期字段做MAX、MIN对比边界抽样取每张表前100行、最后100行以及随机抽样对比关键字段值。如果搭建了Oracle Data Guard还能利用备库做验证。Oracle主备切换的时候有个resolvable gap的概念指备库跟主库之间日志缺口已经补齐到可以安全切换的状态。在迁移阶段如果你已经搭了两套DG库建议把校验查询放在备库执行不影响主库业务同时也能确认DG同步正常。等你后续要做主备切换时先通过V$ARCHIVE_GAP查询缺口确保没有无法解析的日志间隙再做SWITCHOVER。6. 迁移完成后的验证、优化与回滚方案6.1 数据一致性校验行数、汇总和边界值前文提了校验三层法具体落地时我会写成脚本用哈希值对比两张表是否一致。比如在SQL Server端查SELECT COUNT(*), SUM(CHECKSUM(*)) FROM dbo.user_info;在Oracle端查SELECT COUNT(*), SUM(ORA_HASH(user_id || user_name || mobile)) FROM user_info;注意CHECKSUM(*)不能直接对应到Oracle所以我会用源库关键字段拼接后算ORA_HASH保证两边“指纹”能对上。如果两边数量一致但哈希值不一致则需要逐条比对。这个阶段不要怕麻烦我发现大部分同步问题都是边界值引起的比如时间字段的毫秒精度、字符串末尾空格、NULL空值表达方式这些只能靠逐表比对才知道。6.2 统计信息收集与Oracle性能参数调整迁移完成后新Oracle库里的统计信息是空的数据库还不知道表的数据分布。这时候直接上生产全表扫描和糟糕的执行计划会让你怀疑是不是数据搬错了。我对每张核心表都执行一遍BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME CMS, TABNAME USER_INFO, CASCADE TRUE); END;然后再让应用跑一遍典型SQL查询V$SQL里新产生的执行计划重点关注有没有全表扫描。如果发现字段基数低但被当成等值查询频繁使用可以考虑加复合索引。Oracle的PGA_AGGREGATE_TARGET和SGA_TARGET如果偏小大批量导入后会经常出现排序和哈希操作溢出到磁盘响应时间明显变长可以观察V$PGASTAT里的total PGA inauto再决定调整幅度。热词里“oracle查询总金额”这类聚合查询统计信息收集前后速度差别往往会特别大就是因为缺统计信息时走了错误的执行计划。6.3 应用层连接串改造与Spring Boot配置替换如果业务系统是Spring Boot改数据库不是只改URL那么简单。原来连接SQL Server的配置可能长这样spring.datasource.urljdbc:sqlserver://192.168.1.10:1433;DatabaseNamecms spring.datasource.usernamesa spring.datasource.password123456 spring.datasource.driver-class-namecom.microsoft.sqlserver.jdbc.SQLServerDriver spring.jpa.database-platformorg.hibernate.dialect.SQLServer2012Dialect迁移到Oracle后要改成spring.datasource.urljdbc:oracle:thin://192.168.1.20:1521/cms spring.datasource.usernamecms_user spring.datasource.password123456 spring.datasource.driver-class-nameoracle.jdbc.OracleDriver spring.jpa.database-platformorg.hibernate.dialect.Oracle12cDialect这里最容易忽略的是实体主键生成策略。原来用IDENTITY的在Oracle 12c上可以让Hibernate也使用GenerationType.IDENTITY但如果目标库是11g必须换成GenerationType.SEQUENCE并且在实体上指定序列名否则插入时Hibernate会去找hibernate_sequence这个序列而你的DBA很可能没建它。我踩过一次这个坑应用日志里一直报ORA-02289: sequence does not exist就是因为实体类里的序列名和数据库实际序列名没对齐。6.4 预留回滚和容灾DG备库怎么帮你验证迁移完成后最好留一段并行运行期业务可以同时写一份到Oracle但不切流量直到稳定。如果有条件搭Oracle Data Guard要特别注意归档日志和备库的状态。之前搜“oracle主备切换resolvable gap”的人大概就是遇到了备库无法跟上主库的日志缺口。判断能不能切换先看SELECT name, value FROM v$dataguard_stats WHERE name LIKE %lag%;如果transport lag和apply lag都是0同时V$ARCHIVE_GAP查不到记录说明主备已经同步可以安全切换。如果一直有gap多半是网络带宽、归档日志空间或备库的redo应用线程异常不要盲目强制切换否则数据丢失风险很高。在两套DG库的情况下我更愿意把一套作为迁移后的“对照实验场”另一套作为正式容灾。每次升级或数据校验脚本都在备库上先跑一遍发现问题了主库还能继续正常干活。这个思路在迁移后的头几周特别管用相当于给你留了一条快速回退的路。最后说点我个人的体会。数据库迁移这件事真正考验人的不是某个语法不会写而是你有没有耐心把每个细节都验证一遍。技术方案网上到处都有但不同系统之间的业务语义差异只有自己一项项对过才知道。那次迁移做完之后我养成一个习惯任何表结构改动、数据搬迁、SQL改造都先写一份检查清单哪怕再小的操作也按清单过一遍。听起来很繁琐但这正是少踩坑的根源所在。