Modify the order of fields in an Oracle database table

It is said that there is no relationship between the Write Program and the sequence of database table fields, but it is only known after the actual project is done. For database users, the field sequence of database tables may be cumbersome, for example, you need to add fields to the table, but do not want to put the fields at the end of the table. Anyone who has used Oracle knows that it is very cumbersome to change the column name and sequence of tables in ORACLE. Here is a simple method.

Use SYSTEM to log on to sqlplus for operation, because this requires dba

Step 1: query the table id from the data dictionary View

SQL> select object_id from all_objects where owner = 'Scott 'and object_name = 't1 ';

OBJECT_ID
----------
6067

Step 2: Identify the order of all fields in the table by id

SQL> select obj #, col #, name from sys. col $ where obj #= 6067;

OBJ # COL # NAME
--------------------
6067 1 ID
6067 2 NAME

Step 3: update the field order
SQL> update sys. col $ set name = 'new _ id', col # = 3 where obj # = 6067 and name = 'id ';

1 row updated.

SQL> update sys. col $ set name = 'my _ name', col # = 1 where obj #= 6067 and NAME = 'name ';

1 row updated.

SQL> update sys. col $ set col # = 2 where obj #= 6067 and col # = 3;

1 row updated.

SQL> commit;

Finished.

SQL> select * from scott. t1;

ID NAME
------------------------------
3 cheng
2 yong
1 xin
2 gototop
1 topcio
2 yongxin
1 cyx

7 rows selected.

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

SQL> startup
ORACLE instance started.

Total System Global Area 128159368 bytes
Fixed Size 732808 bytes
Variable Size 117440512 bytes
Database Buffers 8388608 bytes
Redo Buffers 1597440 bytes
Database mounted.
Database opened.

SQL> select * from scott. t1;

MY_NAME NEW_ID
------------------------------
Cheng 3
Yong 2
Xin 1
Gototop 2
Topcio 1
Yongxin 2
Cyx 1

7 rows selected.

Step 4. Restart the database service.

Since the data dictionary is loaded into the SQL statement when the database is started, if you use "SELECT * FROM SCOTT. TEST;" after modifying it, it seems that it has not been modified. Therefore, you must restart the database service after the modification.

SQL> SHUTDOWN

SQL> STARTUP
Then, you can see that the modification has been successful.

So far, SCOTT. the column name and sequence in Table T1 have been changed. If you only want to change the sequence and do not want to change the column name, you only need to UPDATE the column name. The reason why it cannot be completed at one time is SYS. COL # And NAME in COL $ are all UNIQUE.

Although this method is risky, it has obvious effects on a particularly large table. However, the general method requires more storage space, rollback segments, and time overhead.