oracle 修改列的前后顺序

Whether you are create table or alter  table plus a filling, the column is asequence of before and after,In oracle you can modify the sequence.

for example:

SQL>create table testcol(id number(3),
id2 int,
id1 int);

Table created.

SQL> select obj#,name from obj$ where name=’TESTCOL’;

OBJ# NAME
———- ——————————
50705 TESTCOL

SQL> DESC TESTCOL;
Name                                                    Null?    Type
————————————————————— ——–

ID                                                               NUMBER(3)
ID2                                                              NUMBER(38)
ID1                                                              NUMBER(38)

SQL> desc col$;
Name                                                             Null?    Type
———————————————————————— ——–

OBJ#                                                             NOT NULL NUMBER
COL#                                                             NOT NULL NUMBER
SEGCOL#                                                          NOT NULL NUMBER
SEGCOLLENGTH                                                     NOT NULL NUMBER
OFFSET                                                           NOT NULL NUMBER
NAME                                                             NOT NULL VARCHAR2(30)
TYPE#                                                            NOT NULL NUMBER
LENGTH                                                           NOT NULL NUMBER
FIXEDSTORAGE                                                     NOT NULL NUMBER
PRECISION#                                                                NUMBER
SCALE                                                                     NUMBER
NULL$                                                            NOT NULL NUMBER
DEFLENGTH                                                                 NUMBER
DEFAULT$                                                                  LONG
INTCOL#                                                          NOT NULL NUMBER
PROPERTY                                                         NOT NULL NUMBER
CHARSETID                                                                 NUMBER
CHARSETFORM                                                               NUMBER
SPARE1                                                                    NUMBER
SPARE2                                                                    NUMBER
SPARE3                                                                    NUMBER
SPARE4                                                                    VARCHAR2(1000)
SPARE5                                                                    VARCHAR2(1000)
SPARE6                                                                    DATE

SQL> select obj#,col#,name from col$ where obj#=50705;

OBJ#       COL# NAME
———- ———- ——————————
50705          1 ID
50705          2 ID2
50705          3 ID1

SQL> update col$ set col#=2 where obj#=50705 and name=’ID1′;

1 row updated.

SQL> update col$ set col#=3 where obj#=50705 and name=’ID2′;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> select obj#,col#,name from col$ where obj#=50705;

OBJ#       COL# NAME
———- ———- ——————————
50705          1 ID
50705          3 ID2
50705          2 ID1

SQL> desc testcol;
Name                                         Null?    Type
—————————————————- ——–

ID                                                    NUMBER(3)
ID2                                                   NUMBER(38)
ID1                                                   NUMBER(38)

SQL> select * from testcol;

no rows selected

SQL> alter system flash shared_pool;
alter system flash shared_pool
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter system flush shared_pool;

System altered.

SQL> select * from testcol;

no rows selected

SQL> desc testcol;
Name                                         Null?    Type
—————————————————- ——–

ID                                                    NUMBER(3)
ID1                                                   NUMBER(38)
ID2                                                   NUMBER(38)

SQL> insert into testcol values(1,2,3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testcol;

ID        ID1        ID2
———- ———- ———-
1          2          3

SQL>