SQL Server到Oracle迁移实战：类型映射、SQL改造与踩坑记录

我去年接了一个迁移项目，一套跑了好几年的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 Oracle（SSMA），很多人不知道。它的优势在于能自动做大量类型映射和语法转换，尤其是存储过程、函数这类对象，能帮你把T-SQL改成PL/SQL的初稿。但注意，自动转换不等于能用，复杂业务逻辑必须人工复查。

我的实际组合是这个：

• 用SSMA做结构迁移和初步SQL转换；
• 用Navicat做数据搬运；
• 用SQL Developer做日常查询、版本对比和验证。

三种工具各有分工，别指望一个工具解决所有问题。特别是SSMA生成的PL/SQL代码，我在多个存储过程里发现它把SQL Server的GETDATE()转成SYSTIMESTAMP，但某些场景下业务需要的是不带时区的SYSDATE，这种语义差异机器是猜不透的。

2. 表结构迁移：类型映射、自增列与默认值的克制处理

2.1 字段类型映射表：照抄会出问题的地方

表结构迁移最常见的问题是类型强行对等。不同数据库的存储模型不一样，直接照抄会在数据量上来后暴露性能问题。我整理了一份核心映射表，迁移时基本对照着用：

这里有个隐藏坑是VARCHAR2的长度。SQL Server的nvarchar(100)按字符数算，Oracle里的VARCHAR2(100)默认按字节算，如果数据库字符集是AL32UTF8，一个中文会占3个字节，这样原来能存100个汉字的字段，迁移后只能存33个。所以字符字段迁移时要人为把长度放大，或者显式使用VARCHAR2(100 CHAR)。

2.2 字符串转数字：隐式转换和NLS格式的双重陷阱

热词里“sqlserver 字符串转数字”搜的人很多，因为两边转换语法确实不一样。SQL Server用CAST或CONVERT：

SELECT CAST('123' AS INT); SELECT CONVERT(DECIMAL(10,2), '123.45');

Oracle用TO_NUMBER：

SELECT 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 ... FETCH，Oracle从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监听服务无法启动”，热词榜上这个词常年在。我第一次也遇到过，后来总结了一条排查链路，遇到基本按顺序走：

1. 用lsnrctl status看当前监听状态，如果报TNS-12541: TNS:no listener，说明监听服务没起来。
2. 用lsnrctl start启动监听，观察输出。如果卡住或直接报错，大概率是listener.ora配置有问题。
3. 检查$ORACLE_HOME/network/log/listener.log里的错误日志，这里能看到具体原因，比如端口被占用、地址绑定失败。
4. 用netstat -ano | findstr :1521确认1521端口是否被占用。有时候是本机别的程序抢占了端口。
5. 检查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.url=jdbc:sqlserver://192.168.1.10:1433;DatabaseName=cms spring.datasource.username=sa spring.datasource.password=123456 spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver spring.jpa.database-platform=org.hibernate.dialect.SQLServer2012Dialect

迁移到Oracle后要改成：

spring.datasource.url=jdbc:oracle:thin:@//192.168.1.20:1521/cms spring.datasource.username=cms_user spring.datasource.password=123456 spring.datasource.driver-class-name=oracle.jdbc.OracleDriver spring.jpa.database-platform=org.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改造，都先写一份检查清单，哪怕再小的操作也按清单过一遍。听起来很繁琐，但这正是少踩坑的根源所在。