How to modify the order of fields in an Oracle database table

This article mainly introduces you to the method of modifying the field order in the Oracle database table. Before introducing the modification method, I first introduce to you the method of creating new fields in the Oracle database table. The article introduces it in detail through the example code. Friends can refer to it. Let’s learn with the editor below. I hope it can help everyone.

Preface

Some time ago, someone asked me a question about adjusting the order of table fields in Oracle. The problem is that after designing the table structure, if you need to add a field to the table later, the field will be placed at the end of the table by default, and there are too many fields and we want to put related fields together. In this case, To modify the field order. Before modifying the order, let's first take a look at how to add fields to the Oracle database table.

New fields:

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

Example

Create table structure:

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

Add a field:

alter table test1
add (name varchar2(30) default ‘无名氏' not null);

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

alter table test1
add (name varchar2(30) default ‘无名氏' not null,

age integer default 22 not null,

has_money number(9,2)

);

Modification of field order

The first original method:

Delete and rebuild. Although this method is simple and crude, it is unscientific

--新建临时表以存储正确的顺序
create table A_2 as select （column1,colum2,……A表中的顺序） from A_1 ;

--删除表A_1
drop table A_1;

--新建A_1并从A_2表中赋予其正确的顺序和值
create table A_1 as select * from A_2;

--删除临时表A_2
drop table A_2;

This method is suitable for tables with fewer fields. It won't be too troublesome, but it will be difficult for tables with many fields.

Second method (recommended):

1. First, you need sys or system permissions to operate

2. Query the ID of the table that needs to be changed

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

Note: ITHOME is the user, TEST is the table to be changed, the table name should be in capital letters

3. Find out the order of all fields in the table through ID

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

4. Modify the order

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 directly Add for update after the statement in the third step to make modifications

Finally commit and restart the Oracle service

Related recommendations:

About Summary of usage of Contains function in Oracle

Detailed explanation of the basic principles of Oracle paging query

Yan Shiba Oracle video resources (source code courseware) sharing

The above is the detailed content of How to modify the order of fields in an Oracle database table. For more information, please follow other related articles on the PHP Chinese website!