If you need to reprint it, please indicate the source!
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.
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.
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, SCOTT. the column name and sequence in table T1 are all set. 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 you cannot do this 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.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.