在數據字典中直接修改Oracle表列名
2024-08-29 13:38:04
供稿：網友
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;
