不像mysql可以直接一条sql搞定(alter table 表名
change 字段名 新字段名 字段类型 默认值 after 字段名(跳到哪个字段之后)),oracle 必须另辟蹊径,曲线救国。
场景描述:假设我们有一张表名为test,该表有三个字段,依次为col1、col2、col3,现在我们想新增一个字段col4,并把col4放到最前面,即我们希望调整后的字段顺序为col4、col1、col2、col3。总结下来方法有三种:
方法一
笔者认为这是最安全保守的一种方法,就是用create table as select 重建新表,此方法适用于表中数据量不大的情况,若表中已经存在了大量数据,则本操作对空间的要求可能就很大了。详细操作如下:
ALTER TABLE test ADD col4 varchar2(5);
ALTER TABLE test RENAME TO test_bk;
CREATE TABLE test AS SELECT col4,col1,col2,col3 FROM test_bk;
方法二
对于表中存在大量数据,并且空间有限的情况,就需要通过直接操作数据字典来实现字段顺序的调整;该方法需要系统管理员权限sys或者 as sysdba。详细操作如下:
select object_id from all_objects where object_name='TEST';或者
select object_id from obj where object_name='TEST';取得表TEST的object_id;假设为51234
select obj#,col#,name from sys.col$ where obj#=51234 order by col#;查看当前字段顺序
update sys.col$ set col#=1 where obj#=51234 and name='COL4';
update sys.col$ set col#=col#+1 where obj#=51234 and name!='COL4';
COMMIT;
SHUTDOWN IMMEDIATE;
STARTUP;
切记本方法需要重启数据库生效!
方法三
该方法的本质实际上也是操作数据字典,但是不需要dba权限,并且此方法仅适用于12c以上版本。
select * from v$version where rownum=1; //12C以上版本可用invisible
ALTER TABLE test MODIFY( COL1 INVISIBLE, COL2 INVISIBLE, COL3 INVISIBLE);
SELECT column_name, column_id FROM user_tab_cols WHERE table_name = 'TEST' order by 2;
ALTER TABLE test ADD ( COL4 VARCHAR2(5));
SELECT column_name, column_id FROM user_tab_cols WHERE table_name = 'TEST' order by 2;
ALTER TABLE test MODIFY( COL1 VISIBLE, COL2 VISIBLE, COL3 VISIBLE);