前几天帮客户处理一个生产问题某系统要给一张三千多万行的订单表加一个flag字段同事按MySQL的惯性直接写了ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0结果跑了半个多小时都没结束undo表空间连续告警业务查询开始卡顿。我上去把语句杀掉然后用一条ALTER TABLE t_order ADD (flag NUMBER(1) DEFAULT 0 NOT NULL)几秒钟就完成了。同一个库、同一张表、同一个字段写法差了一点点表现天差地别。这篇文章就把Oracle加字段、写字段注释这件事从头到尾说清楚适合正在从MySQL/SQL Server转Oracle的开发也适合被生产变更搞得焦头烂额的运维同事参考。先说结论Oracle加字段本身并不难真正的坑全在“带默认值”“大表”“约束”这些关键词组合上。注释更是简单到不行但很多项目里写漏了、写乱了、写出了乱码后面全靠猜字段含义。下面一步步拆。1. 加字段前必须确认的四个事实字典、行数、权限、窗口期很多人在生产库上直接敲ALTER TABLE敲完才发现字段已存在、表有几十亿行、自己权限不够或者一条DDL把业务没提交的事务全给隐式提交了。这些事看起来小每一样都够你加完班回去写事故报告。1.1 字段是否已存在先查USER_TAB_COLUMNS避免ORA-01430Oracle没有MySQL那种ADD COLUMN IF NOT EXISTS语法字段一旦存在你再去加就直接报ORA-01430: column being added already exists in table。这在重复执行迁移脚本、多个人同时改同一个表的时候特别常见。我每次写结构变更脚本之前必查这一句SELECT COUNT(*) FROM user_tab_columns WHERE table_name T_ORDER AND column_name FLAG;返回0再执行加字段返回1就跳过。这个动作虽然简单但它决定了你的脚本能不能安全地重复执行。很多团队用Flyway、Liquibase管理版本但这些工具只保证脚本执行一次不保证两次执行之间的逻辑幂等。万一迭代环境回滚了半个版本、或者有人手滑在生产跑了旧脚本你只能靠这种字典判断兜底。类似的字典视图还有all_tab_columns当前用户能访问的所有表和dba_tab_columns全库需要权限跨Schema判断时记得用owner字段过滤。1.2 表有多大、数据怎么分布决定要不要写DEFAULT这是整篇文章最关键的一个判断。加字段之前先搞清楚表是什么量级SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name T_ORDER;还有一个更暴力的方式直接看段大小SELECT segment_name, bytes/1024/1024 AS size_mb FROM user_segments WHERE segment_name T_ORDER;为什么要看这个因为不同量级的表加字段的策略完全不同。几万行的表随便加几百万行的表还能接受一次几秒钟的锁几千万上亿行的表就必须抠语法细节了。num_rows是ANALYZE或DBMS_STATS之后的数据如果last_analyzed是空的或者很久以前说明统计信息过期这个数字只能当参考最好配合COUNT(*)抽样或者直接看段大小。我习惯的做法是大表预估千万行以上一律先在测试环境复制一份小数据量验证DDL行为再在生产用低峰期执行小表就直接上但也得把语句写规范。1.3 列名与类型选择的Oracle限制30字节、VARCHAR2长度、列位置Oracle的参数MAX_STRING_SIZESTANDARD默认下标识符最长30字节不是30个字符。中文列名按字节算很危险我不建议任何团队用中文或超长语义化列名列名长不代表见名知意反而容易在执行时被各种工具截断搞出怪问题。如果环境是MAX_STRING_SIZEEXTENDED标识符上限能到128字节但不同版本行为有差异没必要为这个赌。字段类型上最容易翻车的是VARCHAR2。12c以前VARCHAR2最大4000字节12c及之后如果开了扩展类型能到32767字节但有个前提条件数据库参数MAX_STRING_SIZEEXTENDED。加字段前先确认这个参数不然你写了VARCHAR2(20000)直接报ORA-00910: specified length too long for its datatypeSELECT value FROM v$parameter WHERE name max_string_size;另外一个从MySQL转过来的人必踩的坑Oracle的ADD COLUMN永远只能把新列加到表末尾没有AFTER xxx这种语法也不支持指定列位置。物理列顺序想调整只能重建表或用视图去掩盖这个后面第5章我会给出取舍建议。1.4 DDL隐式提交与窗口期别让DDL把业务事务“顺手提交”Oracle的DDL语句会隐式提交当前事务。什么意思你在一个会话里先UPDATE了几行业务数据还没COMMIT这时候执行一条ALTER TABLEOracle会先把你之前没提交的UPDATE直接提交掉然后才执行DDL。如果DDL执行失败你之前的UPDATE也收不回来了。这个行为很多人不知道等出了事才反应过来。所以我的硬性规定执行任何DDL之前先显式COMMIT或ROLLBACK收尾当前事务再开始结构变更。生产环境的结构变更窗口也要避开业务高峰尤其是大表操作虽然加字段本身可能很快但万一触发了重写下一章讲整个窗口都会被拖死。权限方面加字段需要表上的ALTER权限或者ALTER ANY TABLE系统权限。查询USER_TAB_COLUMNS不需要额外权限但如果是跨Schema查别人的表要确认对方开了相应授权。2. ALTER TABLE ADD的标准语法与最容易写错的两个地方这章讲语法本身。Oracle的ALTER TABLE ADD和MySQL、SQL Server表面看着差不多实际细节差异不小我按最容易出错的顺序讲。2.1 单列不加括号、多列必须加括号加单个字段下面两种写法都合法ALTER TABLE t_order ADD flag NUMBER(1); ALTER TABLE t_order ADD (flag NUMBER(1));加多个字段必须带上括号字段之间用逗号分隔ALTER TABLE t_order ADD ( flag NUMBER(1), remark VARCHAR2(500), create_ts TIMESTAMP DEFAULT SYSTIMESTAMP );这个括号规则和Oracle的文档风格一致它把所有新增列当成一个“列定义列表”处理。但很多人从MySQL转过来习惯写ADD COLUMNOracle其实也兼容ADD COLUMN关键字吗兼容但不建议依赖因为老版本8i、9i的文档里没这么写后面版本才逐渐接受。为了稳我统一按ADD (col1 type, col2 type)来写多列单列都不出错。2.2 NOT NULL列必须带DEFAULTORA-01758的触发与避免这是Oracle加字段最经典的一个报错。如果你的表里已经有数据执行ALTER TABLE t_order ADD flag NUMBER(1) NOT NULL;大概率碰到ORA-01758: table must be empty to add mandatory (NOT NULL) column。原因很好理解新列加进来之后旧行的这个字段没有任何值你又要求它非空Oracle没法凭空给旧行塞值只能报错。解决办法就是带上DEFAULT让Oracle知道旧行该填什么ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0 NOT NULL;这条语句的意义不只是“给了默认值”它还决定了加字段的执行方式。在11g之后这种“非空列常量默认值”的组合会被Oracle当作元数据操作优化秒级完成。这一点下一章详细展开先记住结论只要逻辑允许大表加非空字段务必写成DEFAULT ... NOT NULL的组合。如果这张表本来就是空表不带DEFAULT直接加NOT NULL也能过。但空表是特例谁也没法保证生产表永远是空的所以脚本里我都默认写上DEFAULT。2.3 与MySQL/SQL Server写法对比为什么注释要单独执行MySQL加字段带注释是一步到位的ALTER TABLE t_order ADD COLUMN flag TINYINT NOT NULL DEFAULT 0 COMMENT 处理标志;Oracle不一样注释必须用独立的COMMENT ON语句ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0 NOT NULL; COMMENT ON COLUMN t_order.flag IS 处理标志;这是Oracle的设计COMMENT不属于列定义的一部分。很多人第一次写Oracle加字段会下意识在列定义后面跟COMMENT结果语法报错半天找不到原因。SQL Server则是用sp_addextendedproperty那一套扩展属性和Oracle的COMMENT ON也完全不同。还有一个常见需求加字段之后紧接着给这个字段加注释。这两条语句务必放在同一个脚本里一起执行。后面第4章细讲注释这里先放一个完整示例ALTER TABLE t_order ADD ( flag NUMBER(1) DEFAULT 0 NOT NULL, remark VARCHAR2(500) ); COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理; COMMENT ON COLUMN t_order.remark IS 处理备注;3. 带默认值加字段为什么能把大表加挂重写机制与实测回到开头那个客户现场这是全篇最核心、最值钱的实战内容。3.1 三种写法的行为差异无DEFAULT/可空带DEFAULT/NOT NULL带DEFAULT同样是ALTER TABLE t_order ADD三种写法在Oracle里的行为完全不一样。我画了一张对比表这是多年踩坑换来的写法旧行数据填充执行方式大表表现ADD (flag NUMBER(1))旧行该字段为NULL纯元数据操作秒级完成几乎无副作用ADD (flag NUMBER(1) DEFAULT 0)旧行逻辑上为0老版本会物理改写全表12c之后部分场景有优化版本差异大可能重写全表产生海量undo/redo锁表ADD (flag NUMBER(1) DEFAULT 0 NOT NULL)旧行逻辑上为0元数据操作默认值存在字典中查询时自动合成秒级完成客户现场踩的就是第二种写法。表三千多万行可空列带DEFAULT在11g的库里执行Oracle为了让你SELECT出旧行也能看到这个字段等于0选择了和UPDATE一样的方式把每一条物理记录都改一遍把默认值写进去。这个过程会产生巨大的undo回滚块和redo重做日志undo表空间告警就是这么来的。为什么第三行不会重写因为列定义了NOT NULLOracle在11g做了一个关键优化既然每一行都必须有值而且默认值是常量那么把默认值记在数据字典里就够了不用真的去动每一个物理块。查询的时候Oracle根据字典里的默认值合成该列的结果给你新插入的数据才真正把这个值物化到数据块里。一句话总结非空约束常量默认值让Oracle有理由偷懒而它把这个懒偷得又快又稳。我实测下来19c对“可空列DEFAULT”的场景也做了优化但不同小版本表现不完全一致。生产环境数据库版本五花八门11g、12c、19c、非容器库、容器库混着来我不建议你把生产安全赌在“版本行为”上。大表加字段默认值要么不写要么就写成DEFAULT ... NOT NULL。3.2 为什么可空带DEFAULT会重写全表物理块改写与undo/redo往深一层讲Oracle的行是存在数据块里的每个块可能有几千行。直接ADD字段不加DEFAULT时块的记录格式不用变Oracle只在字典里加一条元数据而加了可空列的DEFAULT后为了在旧记录里体现这个值Oracle必须逐块重写记录格式把默认值填到每一行的对应位置。这本质上是全表范围的UPDATE只是语法上它藏在DDL里。重写就意味着每个被修改的数据块原像会写进undo表空间如果undo表空间不够大直接报ORA-30036: unable to extend segment by ... in undo tablespace。每个新生成的数据块会产生redo归档模式下还会同步产生归档日志磁盘写满也不奇怪。整个DDL期间表上会有排他锁业务DML全部等待表现为大面积会话堆积、应用超时。这也是为什么很多DBA看到有人在生产大表上执行ALTER TABLE ... ADD ... DEFAULT 0就头皮发麻。不是语句本身多可怕而是它可能在你不知道的情况下触发了一次全表重写。3.3 大表场景下的降级方案先加可空列再分批UPDATE如果业务不允许“非空DEFAULT”比如这个字段加了之后部分旧数据要回填不同的值或者你实在担心版本行为就采用降级方案三步走。第一步先加可空列不带默认值保证秒级完成、不锁业务ALTER TABLE t_order ADD flag NUMBER(1);第二步分批回填数据。不要一条UPDATE更新全表而是按主键范围切片一批一批来DECLARE l_batch_size NUMBER : 10000; l_min_id NUMBER; l_max_id NUMBER; BEGIN SELECT MIN(id), MAX(id) INTO l_min_id, l_max_id FROM t_order; FOR i IN 0 .. TRUNC((l_max_id - l_min_id) / l_batch_size) LOOP UPDATE t_order SET flag 0 WHERE id BETWEEN l_min_id i * l_batch_size AND LEAST(l_min_id (i 1) * l_batch_size - 1, l_max_id) AND flag IS NULL; COMMIT; END LOOP; END; /分批的核心目的每批只产生一小段undo提交后就能释放不会把undo和redo瞬间打满。按主键范围切片比WHERE ROWNUM 10000更可控因为ROWNUM那种写法每次要全表扫描找“剩下没更新的行”越到后面越慢。主键分布均匀的表这个脚本跑起来非常平顺。第三步等回填全部结束再收紧约束ALTER TABLE t_order MODIFY (flag NUMBER(1) DEFAULT 0 NOT NULL);MODIFY加约束同样会校验现有数据但字段值已经都在不再需要全表重写压力小很多。这个三步方案是我在大表变更里用得最多的稳妥路线。3.4 如何判断加字段有没有触发重写监控事务回滚块和redo生产环境操作时你不可能等undo报警了才发现问题。我习惯在执行DDL的同时打开另一个会话盯着这几个指标看SELECT name, value FROM v$mystat WHERE name IN (redo size, undo change vector size);或者直接看当前会话的事务回滚块SELECT s.sid, s.serial#, t.used_ublk, t.used_ublk * t.space AS used_blocks FROM v$transaction t JOIN v$session s ON s.taddr t.addr WHERE s.username IS NOT NULL;used_ublk在正常情况下很小几十几百都正常一旦加字段触发重写这个数字会飞速涨到几万几十万redo size也会持续增大。看到这种趋势赶紧评估要不要中断在11g环境可以直接ALTER SYSTEM KILL SESSION千万别硬等。这也是我一直强调“大表变更必须开监控窗口”的原因。4. COMMENT ON字段注释语法、查看与中文乱码应对字段注释这件事Oracle给的能力非常简单但工程上往往做得最差。很多系统跑了几年表结构全是拼音缩写列名注释一行没有后来接手的同事只能靠猜。加字段的时候顺手把注释写了成本几乎为零收益是长期的结构可读性。4.1 一分钟上手加注释、改注释、清空注释加注释的语法COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理;改注释不需要先删再加重复执行COMMENT ON即可覆盖旧值COMMENT ON COLUMN t_order.flag IS 处理标志0-待处理1-处理中2-已处理;清空注释更简单把内容置空字符串COMMENT ON COLUMN t_order.flag IS ;注意这里的对象名最好带上表名全称不用加Schema名也没关系当前用户执行就够了。如果是跨Schema的表写成COMMENT ON COLUMN schema_name.table_name.column_name IS ...。这里有个细节注释内容会被Oracle存为VARCHAR2(4000字节)不是4000字符。在AL32UTF8字符集下一个中文汉字通常占用3字节所以纯中文注释最大大约1333个字符GBK字符集下一个汉字2字节能到2000个字符。日常注释几百字完全够用但别把一整篇文档塞进去。4.2 从数据字典读注释USER_COL_COMMENTS联表查询模板加了注释之后怎么验证、怎么查核心视图是USER_COL_COMMENTS它只记录当前用户Schema下的字段注释。常用的联表查询SELECT t.table_name, t.column_name, t.data_type, t.nullable, s.comments FROM user_tab_columns t LEFT JOIN user_col_comments s ON s.table_name t.table_name AND s.column_name t.column_name WHERE t.table_name T_ORDER ORDER BY t.column_id;这个查询能一次得到字段的类型、是否可空、注释非常适合加完字段后做结构确认。看整个库所有表的注释情况更实用SELECT table_name, COUNT(*) AS total_cols, COUNT(comments) AS col_with_comment, COUNT(*) - COUNT(comments) AS missing_comment FROM user_col_comments GROUP BY table_name HAVING COUNT(*) - COUNT(comments) 0 ORDER BY missing_comment DESC;这能帮你快速找出哪些表“完全没有注释债”。我接手老系统时第一件事就跑这个看看欠了多少“注释债”。4.3 注释长度、字符集与乱码问题写中文注释后在PL/SQL Developer、SQL Developer里看着正常命令行工具或程序读出来是乱码这种问题十有八九是客户端和数据库字符集不一致。验证方式很简单SELECT value FROM v$nls_parameters WHERE parameter NLS_CHARACTERSET;常见结果是AL32UTF8或ZHS16GBK。客户端连接程序的NLS_LANG要和这个值匹配。Linux下用NLS_LANGSIMPLIFIED CHINESE_CHINA.AL32UTF8连接AL32UTF8库Windows下用PL/SQL Developer时Tools Preferences里设置NLS_LANG为SIMPLIFIED CHINESE_CHINA.AL32UTF8中文注释就不会乱。写注释还有一个很隐蔽的坑如果你在脚本里用IS 清空注释注释列显示的是空NULL而不是空格后续COUNT(comments)统计时不会被计入。想判断某个字段有没有注释直接用comments IS NULL判断别用comments 因为NULL永远不等于空字符串。4.4 把注释写进迁移脚本的工程习惯我强烈建议把“加字段”和“加注释”写成一对永不分离。比如你的迁移脚本叫V20250601_01_add_flag.sql内容应该是-- 需求订单表增加处理标志 ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0 NOT NULL; COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理;文件头写清需求背景文件内ALTER和COMMENT紧紧挨着。这样做的好处是以后任何一个人看版本记录都能从这个脚本里同时知道“改了什么结构”和“这个字段是干嘛的”。很多项目只写ALTER不写COMMENT等字段多了结构文档和实际Schema对不上那时候花十倍时间也补不齐。5. 生产上线前的完整脚本模板与回滚思路最后聊工程化落地把前面几章的语法和避坑点串起来给一个可以直接抄走的上线脚本框架。5.1 幂等脚本模板防重复执行的结构变更这是我最常用的Oracle结构变更PL/SQL模板把“加字段加注释防重复执行”整合在一起DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM user_tab_columns WHERE table_name T_ORDER AND column_name FLAG; IF v_cnt 0 THEN EXECUTE IMMEDIATE ALTER TABLE t_order ADD (flag NUMBER(1) DEFAULT 0 NOT NULL); EXECUTE IMMEDIATE COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理; END IF; END; /几个细节动态SQL里的字符串引号要写双份Oracle的规则就这样初学者最容易在这里报错。整个块放在一个匿名PL/SQL里在SQL*Plus、PL/SQL Developer、sqlcl中都能跑。如果目标是all_tab_columns判断跨Schema表记得加owner条件。EXECUTE IMMEDIATE执行DDL会产生隐式提交所以这个块前后最好不要夹带其他未提交的事务操作。幂等脚本的最大价值同一份脚本在测试环境、预发、生产可以反复跑已经执行过的不会报错没执行过的自动补上。这比YAML里写“仅执行一次”靠谱。5.2 上线后检查清单无效对象、数据回读、默认值行为验证结构变更完成后别急着一键收工。按这个清单检查一遍-- 1. 字段是否到位 SELECT table_name, column_name, data_type, nullable, data_default FROM user_tab_columns WHERE table_name T_ORDER AND column_name FLAG; -- 2. 注释是否到位 SELECT comments FROM user_col_comments WHERE table_name T_ORDER AND column_name FLAG; -- 3. 有没有对象因为结构变更失效 SELECT owner, object_name, object_type, status FROM dba_objects WHERE status INVALID AND object_name IN (SELECT object_name FROM dba_objects WHERE owner USER) AND ROWNUM 20;第3条要注意加字段通常不会让视图或存储过程失效但如果这个字段牵涉到SELECT *类视图的重编译、或者字段类型和某个过程里的变量不匹配还是有可能产生无效对象。查一下没坏处。还要验证一下默认值行为往表里插入一条不指定该字段的数据确认默认值生效查一下旧数据确认该字段能读到预期值。这一步虽然简单但能挡住“结构对了、行为不对”的隐性Bug。5.3 加错了怎么补救DROP COLUMN及其成本结构变更最怕的就是“加完发现字段名写错了”。Oracle的DDL不能回滚但可以再执行一次反向DDL把字段删掉ALTER TABLE t_order DROP COLUMN flag;12c及以上的版本可以加ONLINE减少锁的影响ALTER TABLE t_order DROP COLUMN flag ONLINE;但别把这个当成随便试错的退路。DROP COLUMN如果字段里已经有大量数据会实际去清理这些数据同样可能产生大量undo/redo如果这个字段被视图、物化视图、存储过程引用删完还要处理依赖对象。所以我的建议是加字段前多花十秒钟核对列名和类型永远好过加错了再删。如果表特别大而且担心DROP COLUMN太重还有一个轻量方案把列标记为暂不使用。ALTER TABLE t_order SET UNUSED COLUMN flag;SET UNUSED只做元数据标记不清数据所以快但即便标记了UNUSED物理空间还占着要真正释放还得后续DROP UNUSED COLUMNS。作为紧急兜底可以别当常规手段。最后还是要提醒执行DDL前先把表结构用DBMS_METADATA.GET_DDL导一份留底这是最低成本的结构备份SELECT DBMS_METADATA.GET_DDL(TABLE, T_ORDER) FROM dual;5.4 实在要调整列顺序重建表与视图的取舍很多从MySQL转过来的人加完字段都会问“能把这个新列放到中间某个位置吗”Oracle明确回答不能。普通ALTER TABLE加字段永远追加在末尾。如果业务上真的对列顺序有执念只有两条路一是在低峰期重建表。先创建新表列顺序按你希望的定义然后数据搬迁、改名、重建依赖对象。表小可以大表就算了成本太高风险太大。二是用视图封装。物理表保持默认顺序建一个视图把列顺序投影成你想要的顺序应用层只认视图不认物理表。很多系统就是这么干的灵活且不伤物理存储。这也是我推荐的做法毕竟现代应用应该显式指定列名SELECT *依赖物理顺序本身就不是好习惯。第5章最后再分享一个我自己的使用习惯生产库加字段执行之后我会顺手做一次DBMS_STATS.GATHER_TABLE_STATS吗不一定。加字段不改变已有数据的分布统计信息通常不需要重采但如果加的是“分区表的新分区”或者字段参与了后续的索引创建那么创建完索引后再采集一次比较稳妥。别一上来就无脑GATHER无谓地增加生产负载。这个内容后续还可以扩展成一套完整的Oracle结构变更规范把加索引、改字段类型、分区表DDL都收进来。老规矩遇到拿不准的版本特性先在低版本环境做个10万行的小表实测再谈生产变更。 Oracle大表加字段的坑：DEFAULT NOT NULL与注释最佳实践
前几天帮客户处理一个生产问题某系统要给一张三千多万行的订单表加一个flag字段同事按MySQL的惯性直接写了ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0结果跑了半个多小时都没结束undo表空间连续告警业务查询开始卡顿。我上去把语句杀掉然后用一条ALTER TABLE t_order ADD (flag NUMBER(1) DEFAULT 0 NOT NULL)几秒钟就完成了。同一个库、同一张表、同一个字段写法差了一点点表现天差地别。这篇文章就把Oracle加字段、写字段注释这件事从头到尾说清楚适合正在从MySQL/SQL Server转Oracle的开发也适合被生产变更搞得焦头烂额的运维同事参考。先说结论Oracle加字段本身并不难真正的坑全在“带默认值”“大表”“约束”这些关键词组合上。注释更是简单到不行但很多项目里写漏了、写乱了、写出了乱码后面全靠猜字段含义。下面一步步拆。1. 加字段前必须确认的四个事实字典、行数、权限、窗口期很多人在生产库上直接敲ALTER TABLE敲完才发现字段已存在、表有几十亿行、自己权限不够或者一条DDL把业务没提交的事务全给隐式提交了。这些事看起来小每一样都够你加完班回去写事故报告。1.1 字段是否已存在先查USER_TAB_COLUMNS避免ORA-01430Oracle没有MySQL那种ADD COLUMN IF NOT EXISTS语法字段一旦存在你再去加就直接报ORA-01430: column being added already exists in table。这在重复执行迁移脚本、多个人同时改同一个表的时候特别常见。我每次写结构变更脚本之前必查这一句SELECT COUNT(*) FROM user_tab_columns WHERE table_name T_ORDER AND column_name FLAG;返回0再执行加字段返回1就跳过。这个动作虽然简单但它决定了你的脚本能不能安全地重复执行。很多团队用Flyway、Liquibase管理版本但这些工具只保证脚本执行一次不保证两次执行之间的逻辑幂等。万一迭代环境回滚了半个版本、或者有人手滑在生产跑了旧脚本你只能靠这种字典判断兜底。类似的字典视图还有all_tab_columns当前用户能访问的所有表和dba_tab_columns全库需要权限跨Schema判断时记得用owner字段过滤。1.2 表有多大、数据怎么分布决定要不要写DEFAULT这是整篇文章最关键的一个判断。加字段之前先搞清楚表是什么量级SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name T_ORDER;还有一个更暴力的方式直接看段大小SELECT segment_name, bytes/1024/1024 AS size_mb FROM user_segments WHERE segment_name T_ORDER;为什么要看这个因为不同量级的表加字段的策略完全不同。几万行的表随便加几百万行的表还能接受一次几秒钟的锁几千万上亿行的表就必须抠语法细节了。num_rows是ANALYZE或DBMS_STATS之后的数据如果last_analyzed是空的或者很久以前说明统计信息过期这个数字只能当参考最好配合COUNT(*)抽样或者直接看段大小。我习惯的做法是大表预估千万行以上一律先在测试环境复制一份小数据量验证DDL行为再在生产用低峰期执行小表就直接上但也得把语句写规范。1.3 列名与类型选择的Oracle限制30字节、VARCHAR2长度、列位置Oracle的参数MAX_STRING_SIZESTANDARD默认下标识符最长30字节不是30个字符。中文列名按字节算很危险我不建议任何团队用中文或超长语义化列名列名长不代表见名知意反而容易在执行时被各种工具截断搞出怪问题。如果环境是MAX_STRING_SIZEEXTENDED标识符上限能到128字节但不同版本行为有差异没必要为这个赌。字段类型上最容易翻车的是VARCHAR2。12c以前VARCHAR2最大4000字节12c及之后如果开了扩展类型能到32767字节但有个前提条件数据库参数MAX_STRING_SIZEEXTENDED。加字段前先确认这个参数不然你写了VARCHAR2(20000)直接报ORA-00910: specified length too long for its datatypeSELECT value FROM v$parameter WHERE name max_string_size;另外一个从MySQL转过来的人必踩的坑Oracle的ADD COLUMN永远只能把新列加到表末尾没有AFTER xxx这种语法也不支持指定列位置。物理列顺序想调整只能重建表或用视图去掩盖这个后面第5章我会给出取舍建议。1.4 DDL隐式提交与窗口期别让DDL把业务事务“顺手提交”Oracle的DDL语句会隐式提交当前事务。什么意思你在一个会话里先UPDATE了几行业务数据还没COMMIT这时候执行一条ALTER TABLEOracle会先把你之前没提交的UPDATE直接提交掉然后才执行DDL。如果DDL执行失败你之前的UPDATE也收不回来了。这个行为很多人不知道等出了事才反应过来。所以我的硬性规定执行任何DDL之前先显式COMMIT或ROLLBACK收尾当前事务再开始结构变更。生产环境的结构变更窗口也要避开业务高峰尤其是大表操作虽然加字段本身可能很快但万一触发了重写下一章讲整个窗口都会被拖死。权限方面加字段需要表上的ALTER权限或者ALTER ANY TABLE系统权限。查询USER_TAB_COLUMNS不需要额外权限但如果是跨Schema查别人的表要确认对方开了相应授权。2. ALTER TABLE ADD的标准语法与最容易写错的两个地方这章讲语法本身。Oracle的ALTER TABLE ADD和MySQL、SQL Server表面看着差不多实际细节差异不小我按最容易出错的顺序讲。2.1 单列不加括号、多列必须加括号加单个字段下面两种写法都合法ALTER TABLE t_order ADD flag NUMBER(1); ALTER TABLE t_order ADD (flag NUMBER(1));加多个字段必须带上括号字段之间用逗号分隔ALTER TABLE t_order ADD ( flag NUMBER(1), remark VARCHAR2(500), create_ts TIMESTAMP DEFAULT SYSTIMESTAMP );这个括号规则和Oracle的文档风格一致它把所有新增列当成一个“列定义列表”处理。但很多人从MySQL转过来习惯写ADD COLUMNOracle其实也兼容ADD COLUMN关键字吗兼容但不建议依赖因为老版本8i、9i的文档里没这么写后面版本才逐渐接受。为了稳我统一按ADD (col1 type, col2 type)来写多列单列都不出错。2.2 NOT NULL列必须带DEFAULTORA-01758的触发与避免这是Oracle加字段最经典的一个报错。如果你的表里已经有数据执行ALTER TABLE t_order ADD flag NUMBER(1) NOT NULL;大概率碰到ORA-01758: table must be empty to add mandatory (NOT NULL) column。原因很好理解新列加进来之后旧行的这个字段没有任何值你又要求它非空Oracle没法凭空给旧行塞值只能报错。解决办法就是带上DEFAULT让Oracle知道旧行该填什么ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0 NOT NULL;这条语句的意义不只是“给了默认值”它还决定了加字段的执行方式。在11g之后这种“非空列常量默认值”的组合会被Oracle当作元数据操作优化秒级完成。这一点下一章详细展开先记住结论只要逻辑允许大表加非空字段务必写成DEFAULT ... NOT NULL的组合。如果这张表本来就是空表不带DEFAULT直接加NOT NULL也能过。但空表是特例谁也没法保证生产表永远是空的所以脚本里我都默认写上DEFAULT。2.3 与MySQL/SQL Server写法对比为什么注释要单独执行MySQL加字段带注释是一步到位的ALTER TABLE t_order ADD COLUMN flag TINYINT NOT NULL DEFAULT 0 COMMENT 处理标志;Oracle不一样注释必须用独立的COMMENT ON语句ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0 NOT NULL; COMMENT ON COLUMN t_order.flag IS 处理标志;这是Oracle的设计COMMENT不属于列定义的一部分。很多人第一次写Oracle加字段会下意识在列定义后面跟COMMENT结果语法报错半天找不到原因。SQL Server则是用sp_addextendedproperty那一套扩展属性和Oracle的COMMENT ON也完全不同。还有一个常见需求加字段之后紧接着给这个字段加注释。这两条语句务必放在同一个脚本里一起执行。后面第4章细讲注释这里先放一个完整示例ALTER TABLE t_order ADD ( flag NUMBER(1) DEFAULT 0 NOT NULL, remark VARCHAR2(500) ); COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理; COMMENT ON COLUMN t_order.remark IS 处理备注;3. 带默认值加字段为什么能把大表加挂重写机制与实测回到开头那个客户现场这是全篇最核心、最值钱的实战内容。3.1 三种写法的行为差异无DEFAULT/可空带DEFAULT/NOT NULL带DEFAULT同样是ALTER TABLE t_order ADD三种写法在Oracle里的行为完全不一样。我画了一张对比表这是多年踩坑换来的写法旧行数据填充执行方式大表表现ADD (flag NUMBER(1))旧行该字段为NULL纯元数据操作秒级完成几乎无副作用ADD (flag NUMBER(1) DEFAULT 0)旧行逻辑上为0老版本会物理改写全表12c之后部分场景有优化版本差异大可能重写全表产生海量undo/redo锁表ADD (flag NUMBER(1) DEFAULT 0 NOT NULL)旧行逻辑上为0元数据操作默认值存在字典中查询时自动合成秒级完成客户现场踩的就是第二种写法。表三千多万行可空列带DEFAULT在11g的库里执行Oracle为了让你SELECT出旧行也能看到这个字段等于0选择了和UPDATE一样的方式把每一条物理记录都改一遍把默认值写进去。这个过程会产生巨大的undo回滚块和redo重做日志undo表空间告警就是这么来的。为什么第三行不会重写因为列定义了NOT NULLOracle在11g做了一个关键优化既然每一行都必须有值而且默认值是常量那么把默认值记在数据字典里就够了不用真的去动每一个物理块。查询的时候Oracle根据字典里的默认值合成该列的结果给你新插入的数据才真正把这个值物化到数据块里。一句话总结非空约束常量默认值让Oracle有理由偷懒而它把这个懒偷得又快又稳。我实测下来19c对“可空列DEFAULT”的场景也做了优化但不同小版本表现不完全一致。生产环境数据库版本五花八门11g、12c、19c、非容器库、容器库混着来我不建议你把生产安全赌在“版本行为”上。大表加字段默认值要么不写要么就写成DEFAULT ... NOT NULL。3.2 为什么可空带DEFAULT会重写全表物理块改写与undo/redo往深一层讲Oracle的行是存在数据块里的每个块可能有几千行。直接ADD字段不加DEFAULT时块的记录格式不用变Oracle只在字典里加一条元数据而加了可空列的DEFAULT后为了在旧记录里体现这个值Oracle必须逐块重写记录格式把默认值填到每一行的对应位置。这本质上是全表范围的UPDATE只是语法上它藏在DDL里。重写就意味着每个被修改的数据块原像会写进undo表空间如果undo表空间不够大直接报ORA-30036: unable to extend segment by ... in undo tablespace。每个新生成的数据块会产生redo归档模式下还会同步产生归档日志磁盘写满也不奇怪。整个DDL期间表上会有排他锁业务DML全部等待表现为大面积会话堆积、应用超时。这也是为什么很多DBA看到有人在生产大表上执行ALTER TABLE ... ADD ... DEFAULT 0就头皮发麻。不是语句本身多可怕而是它可能在你不知道的情况下触发了一次全表重写。3.3 大表场景下的降级方案先加可空列再分批UPDATE如果业务不允许“非空DEFAULT”比如这个字段加了之后部分旧数据要回填不同的值或者你实在担心版本行为就采用降级方案三步走。第一步先加可空列不带默认值保证秒级完成、不锁业务ALTER TABLE t_order ADD flag NUMBER(1);第二步分批回填数据。不要一条UPDATE更新全表而是按主键范围切片一批一批来DECLARE l_batch_size NUMBER : 10000; l_min_id NUMBER; l_max_id NUMBER; BEGIN SELECT MIN(id), MAX(id) INTO l_min_id, l_max_id FROM t_order; FOR i IN 0 .. TRUNC((l_max_id - l_min_id) / l_batch_size) LOOP UPDATE t_order SET flag 0 WHERE id BETWEEN l_min_id i * l_batch_size AND LEAST(l_min_id (i 1) * l_batch_size - 1, l_max_id) AND flag IS NULL; COMMIT; END LOOP; END; /分批的核心目的每批只产生一小段undo提交后就能释放不会把undo和redo瞬间打满。按主键范围切片比WHERE ROWNUM 10000更可控因为ROWNUM那种写法每次要全表扫描找“剩下没更新的行”越到后面越慢。主键分布均匀的表这个脚本跑起来非常平顺。第三步等回填全部结束再收紧约束ALTER TABLE t_order MODIFY (flag NUMBER(1) DEFAULT 0 NOT NULL);MODIFY加约束同样会校验现有数据但字段值已经都在不再需要全表重写压力小很多。这个三步方案是我在大表变更里用得最多的稳妥路线。3.4 如何判断加字段有没有触发重写监控事务回滚块和redo生产环境操作时你不可能等undo报警了才发现问题。我习惯在执行DDL的同时打开另一个会话盯着这几个指标看SELECT name, value FROM v$mystat WHERE name IN (redo size, undo change vector size);或者直接看当前会话的事务回滚块SELECT s.sid, s.serial#, t.used_ublk, t.used_ublk * t.space AS used_blocks FROM v$transaction t JOIN v$session s ON s.taddr t.addr WHERE s.username IS NOT NULL;used_ublk在正常情况下很小几十几百都正常一旦加字段触发重写这个数字会飞速涨到几万几十万redo size也会持续增大。看到这种趋势赶紧评估要不要中断在11g环境可以直接ALTER SYSTEM KILL SESSION千万别硬等。这也是我一直强调“大表变更必须开监控窗口”的原因。4. COMMENT ON字段注释语法、查看与中文乱码应对字段注释这件事Oracle给的能力非常简单但工程上往往做得最差。很多系统跑了几年表结构全是拼音缩写列名注释一行没有后来接手的同事只能靠猜。加字段的时候顺手把注释写了成本几乎为零收益是长期的结构可读性。4.1 一分钟上手加注释、改注释、清空注释加注释的语法COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理;改注释不需要先删再加重复执行COMMENT ON即可覆盖旧值COMMENT ON COLUMN t_order.flag IS 处理标志0-待处理1-处理中2-已处理;清空注释更简单把内容置空字符串COMMENT ON COLUMN t_order.flag IS ;注意这里的对象名最好带上表名全称不用加Schema名也没关系当前用户执行就够了。如果是跨Schema的表写成COMMENT ON COLUMN schema_name.table_name.column_name IS ...。这里有个细节注释内容会被Oracle存为VARCHAR2(4000字节)不是4000字符。在AL32UTF8字符集下一个中文汉字通常占用3字节所以纯中文注释最大大约1333个字符GBK字符集下一个汉字2字节能到2000个字符。日常注释几百字完全够用但别把一整篇文档塞进去。4.2 从数据字典读注释USER_COL_COMMENTS联表查询模板加了注释之后怎么验证、怎么查核心视图是USER_COL_COMMENTS它只记录当前用户Schema下的字段注释。常用的联表查询SELECT t.table_name, t.column_name, t.data_type, t.nullable, s.comments FROM user_tab_columns t LEFT JOIN user_col_comments s ON s.table_name t.table_name AND s.column_name t.column_name WHERE t.table_name T_ORDER ORDER BY t.column_id;这个查询能一次得到字段的类型、是否可空、注释非常适合加完字段后做结构确认。看整个库所有表的注释情况更实用SELECT table_name, COUNT(*) AS total_cols, COUNT(comments) AS col_with_comment, COUNT(*) - COUNT(comments) AS missing_comment FROM user_col_comments GROUP BY table_name HAVING COUNT(*) - COUNT(comments) 0 ORDER BY missing_comment DESC;这能帮你快速找出哪些表“完全没有注释债”。我接手老系统时第一件事就跑这个看看欠了多少“注释债”。4.3 注释长度、字符集与乱码问题写中文注释后在PL/SQL Developer、SQL Developer里看着正常命令行工具或程序读出来是乱码这种问题十有八九是客户端和数据库字符集不一致。验证方式很简单SELECT value FROM v$nls_parameters WHERE parameter NLS_CHARACTERSET;常见结果是AL32UTF8或ZHS16GBK。客户端连接程序的NLS_LANG要和这个值匹配。Linux下用NLS_LANGSIMPLIFIED CHINESE_CHINA.AL32UTF8连接AL32UTF8库Windows下用PL/SQL Developer时Tools Preferences里设置NLS_LANG为SIMPLIFIED CHINESE_CHINA.AL32UTF8中文注释就不会乱。写注释还有一个很隐蔽的坑如果你在脚本里用IS 清空注释注释列显示的是空NULL而不是空格后续COUNT(comments)统计时不会被计入。想判断某个字段有没有注释直接用comments IS NULL判断别用comments 因为NULL永远不等于空字符串。4.4 把注释写进迁移脚本的工程习惯我强烈建议把“加字段”和“加注释”写成一对永不分离。比如你的迁移脚本叫V20250601_01_add_flag.sql内容应该是-- 需求订单表增加处理标志 ALTER TABLE t_order ADD flag NUMBER(1) DEFAULT 0 NOT NULL; COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理;文件头写清需求背景文件内ALTER和COMMENT紧紧挨着。这样做的好处是以后任何一个人看版本记录都能从这个脚本里同时知道“改了什么结构”和“这个字段是干嘛的”。很多项目只写ALTER不写COMMENT等字段多了结构文档和实际Schema对不上那时候花十倍时间也补不齐。5. 生产上线前的完整脚本模板与回滚思路最后聊工程化落地把前面几章的语法和避坑点串起来给一个可以直接抄走的上线脚本框架。5.1 幂等脚本模板防重复执行的结构变更这是我最常用的Oracle结构变更PL/SQL模板把“加字段加注释防重复执行”整合在一起DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM user_tab_columns WHERE table_name T_ORDER AND column_name FLAG; IF v_cnt 0 THEN EXECUTE IMMEDIATE ALTER TABLE t_order ADD (flag NUMBER(1) DEFAULT 0 NOT NULL); EXECUTE IMMEDIATE COMMENT ON COLUMN t_order.flag IS 处理标志0-未处理1-已处理; END IF; END; /几个细节动态SQL里的字符串引号要写双份Oracle的规则就这样初学者最容易在这里报错。整个块放在一个匿名PL/SQL里在SQL*Plus、PL/SQL Developer、sqlcl中都能跑。如果目标是all_tab_columns判断跨Schema表记得加owner条件。EXECUTE IMMEDIATE执行DDL会产生隐式提交所以这个块前后最好不要夹带其他未提交的事务操作。幂等脚本的最大价值同一份脚本在测试环境、预发、生产可以反复跑已经执行过的不会报错没执行过的自动补上。这比YAML里写“仅执行一次”靠谱。5.2 上线后检查清单无效对象、数据回读、默认值行为验证结构变更完成后别急着一键收工。按这个清单检查一遍-- 1. 字段是否到位 SELECT table_name, column_name, data_type, nullable, data_default FROM user_tab_columns WHERE table_name T_ORDER AND column_name FLAG; -- 2. 注释是否到位 SELECT comments FROM user_col_comments WHERE table_name T_ORDER AND column_name FLAG; -- 3. 有没有对象因为结构变更失效 SELECT owner, object_name, object_type, status FROM dba_objects WHERE status INVALID AND object_name IN (SELECT object_name FROM dba_objects WHERE owner USER) AND ROWNUM 20;第3条要注意加字段通常不会让视图或存储过程失效但如果这个字段牵涉到SELECT *类视图的重编译、或者字段类型和某个过程里的变量不匹配还是有可能产生无效对象。查一下没坏处。还要验证一下默认值行为往表里插入一条不指定该字段的数据确认默认值生效查一下旧数据确认该字段能读到预期值。这一步虽然简单但能挡住“结构对了、行为不对”的隐性Bug。5.3 加错了怎么补救DROP COLUMN及其成本结构变更最怕的就是“加完发现字段名写错了”。Oracle的DDL不能回滚但可以再执行一次反向DDL把字段删掉ALTER TABLE t_order DROP COLUMN flag;12c及以上的版本可以加ONLINE减少锁的影响ALTER TABLE t_order DROP COLUMN flag ONLINE;但别把这个当成随便试错的退路。DROP COLUMN如果字段里已经有大量数据会实际去清理这些数据同样可能产生大量undo/redo如果这个字段被视图、物化视图、存储过程引用删完还要处理依赖对象。所以我的建议是加字段前多花十秒钟核对列名和类型永远好过加错了再删。如果表特别大而且担心DROP COLUMN太重还有一个轻量方案把列标记为暂不使用。ALTER TABLE t_order SET UNUSED COLUMN flag;SET UNUSED只做元数据标记不清数据所以快但即便标记了UNUSED物理空间还占着要真正释放还得后续DROP UNUSED COLUMNS。作为紧急兜底可以别当常规手段。最后还是要提醒执行DDL前先把表结构用DBMS_METADATA.GET_DDL导一份留底这是最低成本的结构备份SELECT DBMS_METADATA.GET_DDL(TABLE, T_ORDER) FROM dual;5.4 实在要调整列顺序重建表与视图的取舍很多从MySQL转过来的人加完字段都会问“能把这个新列放到中间某个位置吗”Oracle明确回答不能。普通ALTER TABLE加字段永远追加在末尾。如果业务上真的对列顺序有执念只有两条路一是在低峰期重建表。先创建新表列顺序按你希望的定义然后数据搬迁、改名、重建依赖对象。表小可以大表就算了成本太高风险太大。二是用视图封装。物理表保持默认顺序建一个视图把列顺序投影成你想要的顺序应用层只认视图不认物理表。很多系统就是这么干的灵活且不伤物理存储。这也是我推荐的做法毕竟现代应用应该显式指定列名SELECT *依赖物理顺序本身就不是好习惯。第5章最后再分享一个我自己的使用习惯生产库加字段执行之后我会顺手做一次DBMS_STATS.GATHER_TABLE_STATS吗不一定。加字段不改变已有数据的分布统计信息通常不需要重采但如果加的是“分区表的新分区”或者字段参与了后续的索引创建那么创建完索引后再采集一次比较稳妥。别一上来就无脑GATHER无谓地增加生产负载。这个内容后续还可以扩展成一套完整的Oracle结构变更规范把加索引、改字段类型、分区表DDL都收进来。老规矩遇到拿不准的版本特性先在低版本环境做个10万行的小表实测再谈生产变更。