How to modify the field order in an Oracle database table

How to modify the field order in an Oracle database table

Preface

Some time ago, someone asked me a question about the order adjustment of table fields in Oracle. The problem is that after the table structure is designed, if you need to add a field to the table later, the field will be placed at the end of the table by default, in addition, when there are many fields and we want to put them together, we need to modify the field order. Before changing the order, let's take a look at how to add fields in the Oracle database table.

New field:

Syntax for adding fields:alter table tablename add (column datatype [default value][null/not null],….);

Instance

Create a table structure:

create table test1(id varchar2(20) not null);

Add a field:

Alter table test1add (name varchar2 (30) default 'Anonymous 'not null );

Use an SQL statement to add three fields at the same time:

Alter table test1add (name varchar2 (30) default 'Anonymous 'not null, age integer default 22 not null, has_money number (9, 2 ));

Field order modification

The first method is as follows:

Deleted and rebuilt. This method is simple and crude, but not scientific.

-- Create a temporary table to store the correct sequence. create table A_2 as select (column1, colum2 ,...... Sequence in table A) from A_1; -- delete table A_1drop table A_1; -- create A_1 and assign the correct sequence and value to create table A_1 as select * from A_2 from table A_2; -- delete temporary table A_2drop table A_2;

This method is not difficult for tables with fewer fields, but it is difficult for tables with more fields.

Method 2 (recommended ):

1. First, sys or system permissions are required.

2. query the ID of the table to be changed

select object_id from all_objects where owner = 'ITHOME' and object_name = 'TEST';

Note:ITHOME is a user, TEST is the table to be changed, and the table name should be capitalized

3. Identify the order of all fields in the table by ID

select obj#, col#, name  from sys.col$  where obj# = '103756' order by col#

4. Modify the sequence

update sys.col$ set col#=2 where obj#=103756 and name='AGE';update sys.col$ set col#=3 where obj#=103756 and name='NAME';

Or Add the following directly after the statement in step 3.for updateModify

Finally, commit submits and restarts the Oracle service.

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.