oracle 建表字段顺序,【学习笔记】改变Oracle数据表中新增列的位置顺序

天萃荷净

开发DBA反映在Oracle数据表中新增加一列时，显示位置为最后一列。需要改变新增列的顺序，通过修改col$.col#改变列展示顺序。

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

1 row created.

SQL> insert into chf.t_xff values(2,'www.oracleplus.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 oracleplus

2 www.oracleplus.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

------------------------------ ----------

oracleplus 1

www.oracleplus.com 2

SQL> desc chf.t_xff

Name Null? Type

----------------------------------------- -------- ---------------

NAME VARCHAR2(100)

ID NUMBER

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】改变Oracle数据表中新增列的位置顺序