Home >Backend Development >PHP Tutorial >OracleFaq (How to change the column name and order of a table in ORACLE)_PHP tutorial
OracleFaq (How to change the column name and order of a table in ORACLE)_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:22:44930browse
If you need to reprint, please indicate the source! Anyone who has used ORACLE knows that it is a very cumbersome task to change the column names and order of tables in ORACLE. 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 sys.col$ where obj#=6067; OBJ# COL# ---------- ---------- NAME ---------------------------- ---------------------------------- 6067 1 ID 6067 2 NAME 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; 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. 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, just perform UPDATE again. The reason why it cannot be completed at once is because SYS COL# and NAME in .COL$ are both UNIQUE. Although this method has certain risks, the effect is obvious for particularly large tables, while using the general method will require more storage space, rollback segments and time overhead.