联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有网友提出在表中新增加一列,然后让这个列在中间(大家都知道默认情况下,增加一列,这列会在其他列之后),通过修改col$基表可以实现改变列的显示顺序(增加一个新列是小儿科的事情不再研究)。我这里通过建立一个表,有id和name列,现在要改变这两列的默认展示顺序
1.创建模拟表
SQL> create table chf.t_xff (id number,name varchar2(100)); Table created. SQL> insert into chf.t_xff values(1,'xifenfei'); 1 row created. SQL> insert into chf.t_xff values(2,'www.xifenfei.com'); 1 row created. SQL> commit; Commit complete. SQL> desc chf.t_xff; Name Null? Type ----------------------------------------- -------- ------------------- ID NUMBER NAME VARCHAR2(100) SQL> col name for a30 SQL> select * from chf.t_xff; ID NAME ---------- ------------------------------ 1 xifenfei 2 www.xifenfei.com
2.修改col$.col#
SQL> select COLUMN_ID,COLUMN_NAME from dba_tab_cols 2 where table_name='T_XFF' AND OWNER='CHF'; COLUMN_ID COLUMN_NAME ---------- ------------------------------------------------------------ 2 NAME 1 ID SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T_XFF'; OBJECT_ID ---------- 75598 SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598; NAME COL# ------------------------------ ---------- NAME 2 ID 1 SQL> UPDATE COL$ SET COL#=2 WHERE OBJ#=75598 AND NAME='ID'; 1 row updated. SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598; NAME COL# ------------------------------ ---------- NAME 2 ID 2 SQL> UPDATE COL$ SET COL#=1 WHERE OBJ#=75598 AND NAME='NAME'; 1 row updated. SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598; NAME COL# ------------------------------ ---------- NAME 1 ID 2 SQL> COMMIT; Commit complete.
3.验证结果
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 368263168 bytes Fixed Size 1345016 bytes Variable Size 306186760 bytes Database Buffers 54525952 bytes Redo Buffers 6205440 bytes Database mounted. Database opened. SQL> select * from chf.t_xff; NAME ID ------------------------------ ---------- xifenfei 1 www.xifenfei.com 2 SQL> desc chf.t_xff Name Null? Type ----------------------------------------- -------- --------------- NAME VARCHAR2(100) ID NUMBER
通过查询dba_tab_cols视图创建sql确定列的展现顺序是由col$.col#决定