改变表的列的顺序

SQL> connect dbown/dbown@test;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as dbown

SQL> select * from test_column;
 
EMPNO ENAME
----- ----------
 7369 SMITH
 7499 ALLEN
 7521 WARD
 7566 JONES
 7654 MARTIN
 7698 BLAKE
 7782 CLARK
 7788 SCOTT
 7839 KING
 7844 TURNER
 7876 ADAMS
 7900 JAMES
 7902 FORD
 7934 MILLER
 
14 rows selected

SQL> select object_id from dba_objects where object_name = 'TEST_COLUMN';
 
 OBJECT_ID
----------
    117457

SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# = 117457;
 
      OBJ#       COL# NAME
---------- ---------- ------------------------------
    117457          1 EMPNO
    117457          2 ENAME

SQL> UPDATE SYS.COL$ SET COL#=2 WHERE OBJ# = 117457 AND NAME = 'EMPNO';
 
UPDATE SYS.COL$ SET COL#=2 WHERE OBJ# = 117457 AND NAME = 'EMPNO'
 
ORA-01031: insufficient privileges

SQL> CONNECT SYS/DB@TEST AS SYSDBA;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as SYS
 
SQL>  UPDATE SYS.COL$ SET COL#=2 WHERE OBJ# = 117457 AND NAME = 'EMPNO';
 
1 row updated
 
SQL> UPDATE SYS.COL$ SET COL# = 1 WHERE OBJ# = 117457 AND NAME = 'ENAME';
 
1 row updated
 
SQL> COMMIT;
 
Commit complete
 
SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# = 117457;
 
      OBJ#       COL# NAME
---------- ---------- ------------------------------
    117457          2 EMPNO
    117457          1 ENAME
     COL#已经修改了.

SQL> connect dbown/dbown@test
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as dbown
 
SQL> select * from test_column;
 
EMPNO ENAME
----- ----------
 7369 SMITH
 7499 ALLEN
 7521 WARD
 7566 JONES
 7654 MARTIN
 7698 BLAKE
 7782 CLARK
 7788 SCOTT
 7839 KING
 7844 TURNER
 7876 ADAMS
 7900 JAMES
 7902 FORD
 7934 MILLER
 
14 rows selected

查询出来的列的顺序还是没变。

重启数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                   790624 bytes
Variable Size             170651552 bytes
Database Buffers          440401920 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.    

SQL> connect dbown/dbown@test;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as dbown

SQL> select * from test_column;
 
ENAME      EMPNO
---------- -----
SMITH       7369
ALLEN       7499
WARD        7521
JONES       7566
MARTIN      7654
BLAKE       7698
CLARK       7782
SCOTT       7788
KING        7839
TURNER      7844
ADAMS       7876
JAMES       7900
FORD        7902
MILLER      7934
 
14 rows selected

列的顺序已成功修改。                                                                      
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-619994/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13387766/viewspace-619994/