如何在數據字典中修改Oracle的表列名

在Oracle數據庫中沒有提供直接修改表中列名稱的功能，但在實際使用時常需要修改表的列名和列順序，在不得已的情況下，有些Oracle的使用者用重新創建一個新的具有正確列名和順序的數據庫表，再將舊表的數據轉儲進來，最後刪除舊表並將新表重命名為舊表的方法來完成此功能。此方法的最大問題是要求有雙倍的存儲空間、較大的回滾段和較長的時間，如果表中數據量較大，這項工作開銷會很大。實際上我們可以從數據字典中直接修改表列的名稱和順序。下面是實現的具體步驟：

1、以internal用戶名登錄Oracle數據庫，並創建一測試表。

SQL>CREATE TABLE SCOTT.TEST AS SELECT EMPNO,
ENAME FROM SCOTT.EMP;
SQL>DESC SCOTT.TEST 
Name Type Nullable Default Comments 
------- ------------ ------- 
EMPNO NUMBER(4) Y 
ENAME VARCHAR2(10) Y

　
下面需要要把SCOTT.TEST表中EMPNO和ENAME兩列調換順序，並把ENAME列更名為EMP_NAME，EMPNO改為EMP_NO。

2、查詢表中列的實際存儲位置或表。

SQL>SET LONG 9999

由於TEXT列是LONG類型，只有“SET”之後才能完全顯示。

SQL>SELECT TEXT FROM ALL_VIEWS
WHERE VIEW_NAME = ‘USER_TAB_COLUMNS’;

數據字典視圖USER_TAB_COLUMNS中存儲有表列的定義信息，從該語句的查詢結果可以看出，列定義信息是存儲在表SYS.COL$中的，即如果修改表中列的定義，應該在SYS.COL$表中修改。

3、從數據字典視圖ALL_OBJECTS中查找對象SCOTT.TEST對象ID。

SQL> SELECT * FROM ALL_OBJECTS WHERE OWNER =‘SCOTT’
AND OBJECT_NAME=‘TEST’;

4、根據SCOTT.TEST對象的ID，從SYS.COL$檢索出表中列的定義信息。

SQL> 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、重啟數據庫服務。

由於數據字典是在數據庫啟動時加載到SQL中的，所以修改了它之後，如果使用“SELECT * FROM SCOTT.TEST; ”，會發現好像並沒有修改。因此，修改完成之後，還需要重啟數據庫服務。

SQL>SHUTDOWN
SQL>STARTUP

這時，再查看，就會發現修改已經成功。

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

這種方法直接從數據庫中進行表列定義的修改，存在一定風險，但它對於數據量特別大的表是非常有用的。充分利用數據字典功能，往往能夠完成日常很難完成的工作。下面筆者寫了一段簡單的存儲過程，可實現表中列的重命名。大家可以直接調用這個過程來完成列的重命名：

SQL>exec altercolname 
(‘模式名稱’,‘表名稱’,‘原列名稱’,‘新列名稱’); 
create or replace procedure sys.altercolname 
(schmaname in varchar2, 
tabname in varchar2, 
oldcolname in varchar2, 
newcolname in varchar2) is 
n_schmaname varchar2(30); --模式名稱 
n_tablename varchar2(30); --表名稱 
n_oldcolname varchar2(30); --原來列名稱 
n_newcolname varchar2(30); --新的列名稱 
n_objnum number; 
begin 
n_schmaname := upper(schmaname); 
n_tablename := upper(tabname); 
n_oldcolname := upper(oldcolname); 
n_newcolname := upper(newcolname); 
SELECT OBJECT_ID INTO n_objnum 
FROM ALL_OBJECTS 
WHERE OWNER = n_schmaname 
AND OBJECT_NAME=n_tablename; 
UPDATE SYS.COL$ 
SET NAME=n_newcolname 
WHERE OBJ# = n_objnum AND 
NAME=n_oldcolname; 
COMMIT; 
end altercolname;