我们知道，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。
◆查询表中列的实际存储位置或表。
由于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> 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;
|
闽公网安备 35060202000074号