If you need to reprint, please indicate the source!
Anyone who has used ORACLE knows that changing the column names and order of tables in ORACLE is a very tedious task. Here is a simple method for you.
SQL> select object_id from all_objects where owner='SCOTT' and object_name='T1';
OBJECT_ID
----------
6067
SQL> select obj#,col#,name from $ where obj#=6067;
OBJ# COL#
---------- ----------
NAME
------------------------------------------------------------
6067 1
ID
6067 2
NAME
SQL> update $ set name='NEW_ID' ,col#=3 where obj#=6067 and name='ID';
1 row updated.
SQL> update $ set name='MY_NAME',col#=1 where obj#=6067 and name='NAME';
1 row updated.
SQL> update $ set col#=2 where obj#=6067 and col#=3;
1 row updated.
SQL> commit;
Commit complete.
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.
So far we have given the column names and order in the SCOTT.T1 table. If you only want to change the order but not the column names, you just need to do UPDATE again. The reason why it cannot be completed at once is because the COL# and NAME in $ are all UNIQUE.
Although this method has certain risks, it is very effective for particularly large tables, and using general methods will require more storage space, rollback segments and time overhead.