服务热线:13616026886

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

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

在数据字典中修改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;

  ◆重启数据库服务。

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

扫描关注微信公众号