oracle12c 移动列顺序,改变表的列的顺序

SQL> connect ;

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 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

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 ;

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

列的顺序已成功修改。