oracle调整表中列顺序

有一个哥们提出一个问题：

有个表，创建时候的列顺序是a,b,c

如何使用select * 的时候，让列的显示顺序是a,c,b 而且任性地必须使用select *来查询，且不能重建表。

 

假设有个表test

?

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> select * from test;   A                    B                    C -------------------- -------------------- -------------------- you                  pig                  are   SQL> select a,c,b from test;   A                    C                    B -------------------- -------------------- -------------------- you                  are                  pig   SQL>

 

按照其要求，应该是以下显示方式：

?

1 2 3 4 5 6 7

SQL> select * from test;   A                    C                    B -------------------- -------------------- -------------------- you                  are                  pig   SQL>

 

可以通过修改sys.col$表来改变表中列的顺序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

SQL> SELECT owner,object_name,object_id FROM ALL_OBJECTS WHERE OBJECT_NAME='TEST';   OWNER                          OBJECT_NAME                     OBJECT_ID ------------------------------ ------------------------------ ---------- SYS                            TEST                                88842   SQL> select obj#,col#,name from sys.col$ where obj# =88842 order by col#;         OBJ#       COL# NAME ---------- ---------- ------------------------------      88842          1 A      88842          2 B      88842          3 C   SQL> update sys.col$ set col#=2 where obj# =88842 and name='C';   1 row updated.   SQL> update sys.col$ set col#=3 where obj# =88842 and name='B';   1 row updated.   SQL> commit;   Commit complete.   SQL> select * from test;   A                    C                    B -------------------- -------------------- -------------------- you                  are                  pig   SQL>