9.3 维护数据表 · 波仔的 AI 编程实战课

📄 一句话概括

表的日常维护有六件事：加字段、改字段、删字段、改名、换表空间、清数据。 每个动作的「快不快」差别巨大——有的几毫秒，有的要锁表几小时。 两条最实用的经验：大表删字段先 SET UNUSED 再清理； DELETE 不降高水位，TRUNCATE 才降。

📝 本章和前面几章的关系

9.2 讲怎么建表。这一篇讲建完之后怎么改。 改表结构是运维里最容易出事的一类操作——因为它经常要锁表，而锁表就意味着业务停摆。 这一篇的每个动作都对应一个「能不能在线做」的判断。 12c 之后很多操作支持在线（ONLINE 关键字），但老库没有。

一、增加和删除字段

加字段：ALTER TABLE emp ADD (email VARCHAR2(50)); 11g 之后，带常量默认值的加列【不再重写全表】—— 默认值存在字典里，读的时候现填，所以几毫秒就完成。 但默认值必须是常量。 DEFAULT SYSDATE 这种「每行可能不同」的值还会重写全表， 因为它没法用一个字典里的值代表所有行。 大表上加这种列，等于全表重写一遍，要挑窗口做。 删字段两条路： DROP COLUMN 立刻重写全表（要把那列数据从每个块里抠掉，很慢、很占 undo）； SET UNUSED COLUMN 只在字典里打个标志，几毫秒。 所以大表的正确做法是「先 SET UNUSED，低峰再 DROP UNUSED COLUMNS」。 这是「先摘牌子，后拆房子」的套路。 注意 SET UNUSED 之后那列就查不到了（select * 也不显示），但空间没还给你。

二、修改字段：类型只能往宽了改

变宽（VARCHAR2(10) → VARCHAR2(30)）随时可以。 变窄只有在该列全为 NULL 时才允许，否则报 ORA-01441。 改类型更严格： 数字转字符一般可以，字符转数字要看内容——只要有一行不是数字就失败（ORA-01722）。 所以改类型之前先跑一句「查有没有不合法的值」，比直接改安全得多。 改列的隐藏成本：它会让依赖这列的视图、存储过程变成 INVALID。 改完记得查一遍：select object_name from user_objects where status = 'INVALID'; 还有一个「NOT NULL 的坑」：给已有数据的表加 NOT NULL 约束，如果列里有 NULL 会失败—— 正确顺序是「先填满值，再加约束」，不是反过来。

三、重命名表

ALTER TABLE emp RENAME TO emp_bak; 改的是字典里的名字，几毫秒完事，不动数据。 但要注意三件事： ① 视图、存储过程、同义词里引用的还是旧名字，会变成无效。 ② 外键约束的名字不会跟着改（可能还叫 FK_EMP_DEPT）。 ③ 应用里的 SQL 如果写死了表名，全部报错。 所以重命名在生产上是个「看着简单、牵连很广」的操作。 稳妥做法是先建同义词指向新表，让应用继续用旧名字，等改完代码再删同义词。 这一招叫「同义词过渡」，第 10 章会讲同义词。

四、改变表空间和存储参数

ALTER TABLE emp MOVE TABLESPACE users; 它相当于「新建一个段、搬数据、删旧段」， 所以很慢、要两倍空间、而且【搬完之后所有索引都失效】。 索引失效这一点最容易忘。 因为索引里存的是「行在哪个块」的地址（ROWID），表一搬，地址全变。 所以 MOVE 之后必须 ALTER INDEX ... REBUILD 重建所有索引， 否则查询会走「失效索引」，直接报 ORA-01502。 MOVE 还有个附带好处：它会重新排布数据、消除碎片。 所以「表空间碎片多了」的标准治法就是 MOVE 一次，效果相当于「整理房间」。

五、删除表：DROP 与回收站

DROP TABLE emp; 删表。 但 10g 之后它默认是「挪进回收站」—— 表还在，只是改了个名字（BIN$...），还能 FLASHBACK TABLE emp TO BEFORE DROP 找回来。 真要删干净，用 DROP TABLE emp PURGE; 或者清空回收站 PURGE RECYCLEBIN;。 回收站占的空间是实打实的——删了一堆大表却没释放空间，八成是它们在回收站里躺着。 查回收站：select object_name, original_name from user_recyclebin; 这一句该在「删完发现磁盘没空出来」时第一句就跑。

六、修改表的状态：READ ONLY

ALTER TABLE emp READ ONLY;（11g 起） 数据不能改，但可以 DROP／TRUNCATE。 它比表空间级 READ ONLY 更细： 一个表空间里几十张表，只把其中几张设成只读，这在「历史数据归档但要留查询」的场景下很有用。 注意 READ ONLY 只挡 DML，不挡 DDL。 DROP 和 TRUNCATE 照样能做——所以它防的是「误改数据」，不防「误删表」。 要防误删，得靠权限控制（不给 DROP ANY TABLE），不是靠 READ ONLY。

七、清空数据：DELETE、TRUNCATE、DROP 三档

操作	数据	高水位	能回滚
`DELETE`	删（可带条件）	不降	能
`TRUNCATE`	全删	降回 0	不能
`DROP`	连表删	—	能（回收站）

TRUNCATE 是 DDL，不能回滚（第 4 章讲过）。 它还不触发 DELETE 触发器—— 所以「删数据顺便记日志」的触发器，TRUNCATE 之后日志里什么都没有。 三者的性能差得很远： DELETE 要逐行删、逐行记 undo、逐行触发触发器； TRUNCATE 直接改数据字典里的段信息，几毫秒。 一亿行的表 DELETE 要几小时，TRUNCATE 不到一秒。

八、动手 Demo：真跑一遍高水位线：DELETE 之后文件一点没少

沙箱里没有 Oracle，ALTER TABLE 的很多动作执行不了。 但「DELETE 不降高水位」这件事，用一个 sqlite 文件能测得非常实—— 因为 sqlite 也有「删了不缩文件」的行为。 我插两万行看文件多大，全删再看一次，最后 VACUUM 再看一次，三个数字就是高水位线的实证。

import os, sqlite3, tempfile

d = tempfile.mkdtemp()
p = os.path.join(d, 'hwm.db')
db = sqlite3.connect(p)
db.execute('CREATE TABLE big (id INT, pad TEXT)')
db.executemany('INSERT INTO big VALUES (?,?)', [(i, 'x' * 200) for i in range(20000)])
db.commit()
db.close()
print('插入两万行后：', os.path.getsize(p), '字节')

db = sqlite3.connect(p)
db.execute('DELETE FROM big')
db.commit()
db.close()
print('DELETE 全表后：', os.path.getsize(p), '字节（一点没少）')

db = sqlite3.connect(p)
db.execute('VACUUM')
db.close()
print('VACUUM 之后：', os.path.getsize(p), '字节')

# 三档的语义差别
for op, data, hwm in (('DELETE', '删', '不降'), ('TRUNCATE', '删', '降回 0'), ('DROP', '连表删', '—')):
    print('%-9s 数据 %s  高水位 %s' % (op, data, hwm))

实跑输出：

--------------------------------------------------------------------
第四十一课：维护数据表——加列、改列、挪位置、清空
--------------------------------------------------------------------

  **表建好之后不会一直不动。加字段、改类型、换表空间、清数据，都是日常。**
  **这一篇的每个动作都有一个「快不快」的问题——**  **有的几毫秒，有的要锁表几小时。**

  加字段：11g 之后带默认值也很快
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | ALTER TABLE emp ADD (email VARCHAR2(50));
  | ALTER TABLE emp ADD (hired DATE DEFAULT SYSDATE);
    11g 起，带默认值的加列不再重写全表。

  **11g 之前，`ADD COLUMN ... DEFAULT 值` 会把全表每一行都改一遍**——
  **一亿行的表要跑几个小时，还得锁表。**  **11g 之后改成「默认值存在字典里」，读的时候现填**，
  **所以几毫秒就完成。**
  **但有个前提：默认值必须是常量。**
  **`DEFAULT SYSDATE` 这种「每行可能不同」的值，还是会重写全表**——
  **因为它没法用「一个字典里的值」代表所有行。**

  改字段：类型只能往宽了改
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | ALTER TABLE emp MODIFY (ename VARCHAR2(30));
  | ALTER TABLE emp MODIFY (sal NUMBER(9,2));
  | ALTER TABLE emp MODIFY (ename NOT NULL);
    变宽随便改，变窄要先清空。

  **变宽（`VARCHAR2(10)` → `VARCHAR2(30)`）随时可以，不影响已有数据。**
  **变窄（`VARCHAR2(30)` → `VARCHAR2(10)`）只有在该列全为 NULL 时才允许**，
  **否则报 `ORA-01441`（column to be modified must be empty）。**
  **改类型更严格：数字转字符可以，字符转数字要看内容。**
  **`VARCHAR2` 转 `NUMBER` 时，只要有一行不是数字就失败**（`ORA-01722`）。
  **所以改类型之前先跑一句「看有没有不合法的值」，比直接改安全得多。**
  **改列还有个隐藏成本：它会让「依赖这个列的视图、存储过程」变成无效。**
  **改完记得 `select * from user_objects where status = 'INVALID'` 查一遍。**

  删字段：DROP 还是 SET UNUSED
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | ALTER TABLE emp DROP COLUMN email;
  | ALTER TABLE emp SET UNUSED COLUMN email;
  | ALTER TABLE emp DROP UNUSED COLUMNS;
    大表推荐先 SET UNUSED。

  **`DROP COLUMN` 会立刻重写全表**——
  **因为它要把那一列的数据从每个块里抠掉。**  **大表上跑这个，可能要几个小时，期间还占着 undo。**
  **`SET UNUSED` 只是「标记这一列不用了」，字典里改个标志，几毫秒完事。**
  **之后在业务低峰跑 `DROP UNUSED COLUMNS` 真正清理。**  **这是一个「先摘牌子，后拆房子」的套路**
  ，大表维护都这么干。
  **`SET UNUSED` 之后那一列就查不到了（`select *` 也不显示），但它还占着空间**——
  **所以别以为 `SET UNUSED` 就等于删了，空间没还给你。**

  重命名、换表空间、改状态
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | ALTER TABLE emp RENAME TO emp_bak;
  | ALTER TABLE emp MOVE TABLESPACE users;
  | ALTER TABLE emp READ ONLY;
    MOVE 之后索引会失效，要重建。

  **`RENAME` 改的是字典里的名字，几毫秒。**
  **但要注意：视图、存储过程里引用的还是旧名字，会变成无效。**
  **`MOVE TABLESPACE` 会「把整张表重写一遍」**——
  **它相当于「新建一个段、把数据搬过去、删掉旧的」。**
  **所以它很慢、很占空间（要两倍空间），而且搬完之后所有索引都失效**，
  **必须 `ALTER INDEX ... REBUILD` 重建。**
  **`READ ONLY` 表是 11g 的新东西：数据不能改，但可以 `DROP`／`TRUNCATE`。**
  **它比表空间级 `READ ONLY` 更细**——  **一个表空间里可以有几十张表，只把其中几张设成只读。**

  删数据：DELETE、TRUNCATE、DROP 三档
  --------------------------------------------------------------------
  **插两万行（每行 200 字节填充），文件现在 4333568 字节。**
  **`DELETE` 全表之后，文件还是 4333568 字节——一点没少。**
  **这就是 Oracle 里的「高水位线」：删了数据，水位不降。**
  **以后插入会复用这些空块，所以「删了再插」不会让文件变大**——
  **但「删了不插」也不会让文件变小。**
  **`VACUUM`（对应 Oracle 的 `SHRINK` 或重建）之后，文件缩到 8192 字节。**
  **所以三档的语义是：**
  **`DELETE` 删数据、不降水位；`TRUNCATE` 删数据、把水位降回 0；`DROP` 连表和段一起删。**
  **`TRUNCATE` 是 DDL，不能回滚**（第 4 章讲过这一条），
  **而且它不触发 `DELETE` 触发器**——
  **所以「删表数据顺便记日志」的触发器，`TRUNCATE` 之后日志里什么都没有。**

第一步 4333568 字节，第二步还是 4333568 字节。 一点没少——这就是「删了数据，水位不降」。 Oracle 里对应的是 USER_TABLES.BLOCKS 和 EMPTY_BLOCKS 这两列： 前者是「高水位线以下的块数」，后者是「其中从没用过的」。 DELETE 全表之后 BLOCKS 不变，EMPTY_BLOCKS 也不变——因为水位线没动。 第三步 VACUUM 之后缩到 8192 字节（只剩一个空页）。 Oracle 里对应的动作是 ALTER TABLE ... SHRINK SPACE（要开行迁移）或者 MOVE 重建。 两者都「真的把空间还回去」，但都很慢、都要额外空间。 这就是为什么「删了历史数据但磁盘没空」是 DBA 的日常问题。 治法不是再删一遍，是 SHRINK 或者 MOVE。 最后三行把三档的语义并排摆出来： DELETE 删数据不降水位、TRUNCATE 降回 0、DROP 连表一起删。 记住这一行，能省掉很多「为什么磁盘没空」的排查。

30 秒实验

把 range(20000) 改成 range(2000)，重跑。文件从 4.3MB 降到 0.5MB 左右。 但「删完不变、VACUUM 之后缩」这个规律一样。
把 DELETE FROM big 改成 DELETE FROM big WHERE id < 10000，重跑。 文件还是不变——删一半也是删，水位照样不降。
把 VACUUM 那三行删掉，重跑。最后一行会显示「VACUUM 之后」和「DELETE 后」一样大。 这就是「不做 SHRINK 就永远不还空间」的直接证据。

常见误区

大表上直接 DROP COLUMN。 要重写全表，可能锁几小时。先 SET UNUSED，低峰再清理。
ALTER TABLE MOVE 之后忘了重建索引。 索引里存的是 ROWID， 表一搬地址全变，索引全部失效，查询报 ORA-01502。
DELETE 全表之后以为空间回来了。 高水位不降，要 SHRINK 或 MOVE 才真还空间。
DROP TABLE 之后以为数据没了。 10g 起默认进回收站，DROP TABLE ... PURGE 才是真删。

自测三问（点开对答案）

1. 为什么 `DELETE` 全表之后空间没回来？ —— **高水位线不降。** 删的只是数据，块还挂在段上。**要 `SHRINK SPACE` 或 `MOVE` 才真还空间。** 2. `SET UNUSED` 和 `DROP COLUMN` 有什么区别？ —— **`SET UNUSED` 只改字典标志，几毫秒；`DROP COLUMN` 重写全表，很慢。** **但 `SET UNUSED` 不还空间**，要再跑 `DROP UNUSED COLUMNS`。 3. `ALTER TABLE ... MOVE` 之后必须做什么？ —— **重建所有索引。** 索引存的是 `ROWID`，表一搬地址全变， **不重建就报 `ORA-01502`（索引不可用）。** 4. `TRUNCATE` 为什么比 `DELETE` 快那么多？ —— **它不改数据，只改数据字典里的段信息**，几毫秒完成。 **代价是不能回滚、不触发 `DELETE` 触发器。**

下一篇讲约束：非空、主键、唯一、外键——让数据库替你把住数据质量。

☐ （本篇完，下一篇 9.4 数据完整性和约束性）