惜分飞

有网友提出在表中新增加一列，然后让这个列在中间(大家都知道默认情况下，增加一列，这列会在其他列之后)，通过修改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