Change the order of fields after adding fields to Oracle table

At work: After adding a field to a table, add a new field at the end, you want to adjust the position of the new field.

1, the original method:

--Create a new temporary table to store the correct orderCreate TableA_2 as Select(Column1,colum2,...... Order in Table A) froma_1;--Delete Table A_1Drop Tablea_1;--Create a new a_1 and give it the correct order and value from the a_2 tableCreate TableA_1 as Select *  froma_2;--Delete temporary table a_2Drop Tablea_2;

This approach does not seem too cumbersome for tables with fewer fields, but it is difficult for tables with more fields.

2. Using the System database

[1] First, log on PL/SQL as the SYS user

[2] Check out the ID of the table you want to change, take my local data for example, I want to change the ' finace ' user under the ' INCOME ' this table. Note: The tables in Oracle are capitalized.

Select     object_id          from         all_objects        where     ='finace'and  object_name='INCOME' ;

Get income This table of I,:

　　

[3] According to the ID to query the table field and the field ordinal

Select      obj#,col#,name        from        sys.col$                 where     obj#= 73626

The results are as follows:

　　

[4] The second column is the ordinal of the field, you can use the UPDATE statement to arbitrarily adjust the order of the fields.

Change the order of fields after adding fields to Oracle table