在資料字典中直接修改Oracle表列名(收集)

Oracle資料庫沒有提供直接修改表中列名稱的功能，但在實際使用時常需要修改表的列名和列順序 ---實際上我們可以從資料字典中直接修改表列的名稱和順序

[@more@]

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

這種方法直接從資料庫中進行表列定義的修改，存在一定風險，但它對於資料量特別大的表是非常有用的。充分利用資料字典功能，往往能夠完成日常很難完成的工作。下面是儲存過程，可實現表中列的重新命名。可直接呼叫此過程來完成列的重新命名

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;
/
--exec altercolname(‘模式名稱’,‘表名稱’,‘原列名稱’,‘新列名稱’);

exec altercolname('NCR_0412','TTT','ADDNAME','P_ADDNAME');