如何透过数据字典直接修改 Oracle 表列名与顺序

背景与痛点

Oracle 数据库未提供 ALTER TABLE ... RENAME COLUMN 语法（注：Oracle 9i 后已支持，但早期版本不支持），传统做法需建立新表、转储数据、删除旧表、重命名新表，耗费双倍存储空间、大量回滚段与时间。针对大型表，可直接更新数据字典基表 SYS.COL$ 实现列名与顺序的即时修改，风险虽存但效益显著。

实操步骤（以 SCOTT.TEST 表为例）

1. 建立测试表：

   CREATE TABLE SCOTT.TEST AS SELECT EMPNO, ENAME FROM SCOTT.EMP;

   目标：将 EMPNO 与 ENAME 互换顺序，并分别更名为 EMP_NO、EMP_NAME。
2. 查询列定义存储位置：

   SET LONG 9999
   SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME = 'USER_TAB_COLUMNS';

   结果显示列定义存储于 SYS.COL$。
3. 取得对象 ID：

   SELECT OBJECT_ID FROM ALL_OBJECTS WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'TEST';

   假设返回 13888。
4. 检索列定义：

   SELECT OBJ#, COL#, NAME FROM SYS.COL$ WHERE OBJ# = 13888;

   返回：

   OBJ#     COL# NAME
   -------- ------ ------
   13888       1 EMPNO
   13888       2 ENAME

5. 执行 UPDATE 修改：

   UPDATE SYS.COL$ SET COL# = 2, NAME = 'EMP_NO' WHERE OBJ# = 13888 AND NAME = 'EMPNO';
   UPDATE SYS.COL$ SET COL# = 1, NAME = 'EMP_NAME' WHERE OBJ# = 13888 AND NAME = 'ENAME';
   COMMIT;

6. 重启数据库实例：数据字典于启动时载入内存，必须 SHUTDOWN / STARTUP 后生效。
7. 验证结果：

   SELECT * FROM SCOTT.TEST;
   EMP_NAME    EMP_NO
   ----------  ------
   SMITH          7369
   ALLEN          7499
   WARD           7521
   ...

可重复使用的存储过程

CREATE OR REPLACE PROCEDURE SYS.ALTERCOLNAME (
    SCHMANAME IN VARCHAR2,  -- 模式名称
    TABNAME   IN VARCHAR2,  -- 表名称
    OLDCOLNAME IN VARCHAR2, -- 原列名称
    NEWCOLNAME IN VARCHAR2  -- 新列名称
) IS
    N_OBJNUM NUMBER;
BEGIN
    SELECT OBJECT_ID INTO N_OBJNUM
    FROM ALL_OBJECTS
    WHERE OWNER = UPPER(SCHMANAME)
      AND OBJECT_NAME = UPPER(TABNAME);

    UPDATE SYS.COL$
    SET NAME = UPPER(NEWCOLNAME)
    WHERE OBJ# = N_OBJNUM
      AND NAME = UPPER(OLDCOLNAME);

    COMMIT;
END ALTERCOLNAME;
/
-- 调用示例
EXEC ALTERCOLNAME('SCOTT', 'TEST', 'EMPNO', 'EMP_NO');

风险提示

• 直接更新 SYS.COL$ 属非官方支持操作，可能导致数据字典不一致，建议仅在非生产环境或经完整备份后尝试。
• Oracle 9i 及以上版本已原生支持 ALTER TABLE ... RENAME COLUMN，优先使用官方语法。