服务热线:13616026886

技术文档 欢迎使用技术文档,我们为你提供从新手到专业开发者的所有资源,你也可以通过它日益精进

位置:首页 > 技术文档 > 数据库技术 > Oracle技术 > Oracle开发 > 查看文档

怎样在数据字典中直接修改oracle表列名

我们知道,oracle数据库中没有提供直接修改表中列名称的功能,但在实际使用时常需要修改表的列名和列顺序,在不得已的情况下,有些oracle的使用者用重新创建一个新的具有正确列名和顺序的数据库表,再将旧表的数据转储进来,最后删除旧表并将新表重命名为旧表的方法来完成此功能。此方法的最大问题是要求有双倍的存储空间、较大的回滚段和较长的时间,如果表中数据量较大,这项工作开销会很大。实际上我们可以从数据字典中直接修改表列的名称和顺序。下面是实现的具体步骤:

◆以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。

◆查询表中列的实际存储位置或表。

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$表中修改。

◆从数据字典视图all_objects中查找对象scott.test对象id。

sql> select * from all_objects where owner =‘scott’ 
    and object_name=‘test’;

◆根据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

◆使用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;

1 2 下一页>>


◆重启数据库服务。

由于数据字典是在数据库启动时加载到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;

扫描关注微信公众号