如何在数据字典中修改Oracle的表列名(col$用于查看表都有什么列名)
来源:互联网 发布:java判断目录是否存在 编辑:程序博客网 时间:2024/05/19 05:39
在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 ENAME5、使用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;
AND OBJECT_NAME=‘TEST’;2、根据SCOTT.TEST对象的ID,从SYS.COL$检索出表中列的定义信息。
SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# =13888;3、使用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’;
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 ENAME5、使用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;
用到的数据字典:
1、从数据字典视图ALL_OBJECTS中查找对象SCOTT.TEST对象ID。
AND OBJECT_NAME=‘TEST’;2、根据SCOTT.TEST对象的ID,从SYS.COL$检索出表中列的定义信息。
SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# =13888;3、使用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’;
- 如何在数据字典中修改Oracle的表列名(col$用于查看表都有什么列名)
- 数据字典表col$用于查看表都有什么列名
- 如何在数据字典中修改Oracle的表列名
- 在数据字典中直接修改Oracle表列名
- 在数据字典中直接修改Oracle表列名
- Oracle修改表列名与顺序的解决方案 (sql 修改列名)
- DB2查看表数据字典,查看表列名
- Oracle中修改表列名,用SQL语句的方式
- Oracle中修改表列名,用SQL语句的方式
- mysql(修改表名,列名,添加表列,删除表列,修改列类型,修改表列名)
- oracle怎样修改表名、列名、字段类型、添加表列、删除表列
- oracle修改表名、列名、字段类型、添加表列、删除表列
- oracle怎样修改表名、列名、字段类型、添加表列、删除表列
- oracle怎样修改表名、列名、字段类型、添加表列、删除表列
- oracle怎样修改表名、列名、字段类型、添加表列、删除表列、主键和unique
- oracle 修改表名、列名、字段类型、添加表列、删除表列 等相关操作
- oracle怎样修改表名、列名、字段类型、添加表列、删除表列
- oracle怎样修改表名、列名、字段类型、添加表列、删除表列
- 异步提交的方法
- Ajax请求处理Session超时
- 实现展开列ExpandableListView的三种方式之SimpleExpandableListAdapter实例
- POJ 2485 Highways 无向图最小生成树求最长的边
- RoboGuice使用
- 如何在数据字典中修改Oracle的表列名(col$用于查看表都有什么列名)
- 说说Xcode4中xib绑定的原理
- 程序员求职之道(《程序员面试笔试宝典》)之如何与企业进行待遇谈判?
- jQuery 导航条动态美化效果
- 求给定字符串中包含的字符及个数及最多字符是哪个?
- Linux下C语言多线程
- oracle数据查找方式以及索引的分类和索引失效的情况-------帮助了解索引从而优化我们oracle数据库
- 四种格式的以太网帧结构
- MySQL主主(主从)同步配置详解