如何在数据字典中修改Oracle的表列名

最新推荐文章于 2022-11-07 15:33:47 发布

haiross

最新推荐文章于 2022-11-07 15:33:47 发布

阅读量1.3k

点赞数

分类专栏： Oracle数据库基本管理 oracle转载博文未看 oracle字符集问题 oracle开发SQL语句 oracle常用实验或管理脚本

Oracle数据库基本管理同时被 3 个专栏收录

337 篇文章 6 订阅

订阅专栏

oracle转载博文未看

321 篇文章 0 订阅

订阅专栏

oracle开发SQL语句

274 篇文章 13 订阅

订阅专栏

如何在数据字典中修改Oracle的表列名

在Oracle数据库中没有提供直接修改表中列名称的功能，但在实际使用时常需要修改表的列名和列顺序这一情况下，本文给出了从数据字典中直接修改表列的名称和顺序的方法，供大家参考！

AD：51CTO学院：IT精品课程在线看！

在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;

【相关文章】