Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to modify the order of fields in the Oracle database table, which is very detailed and has a certain reference value. Friends who are interested must read it!

New fields:

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

Example

Create a table structure:

Create table test1 (id varchar2 (20) not null)

Add a field:

Alter table test1add (name varchar2 (30) default 'John Doe' not null)

Add three fields at the same time using a SQL statement:

Alter table test1add (name varchar2 (30) default 'John Doe' not null,age integer default 22 not null,has_money number)

Modification of field order

The first primitive method:

Delete and rebuild, this method is simple and rough, but it is not scientific.

-- create a new temporary table to store the correct order of create table Agg2 as select (column1,colum2,... The order in table A) from Aban 1 as select-Delete table A_1drop table Aban 1 transpose color-create a new Aban 1 and give it the correct order and value from the Aban 2 table create table Aban 1 as select * from Amitable2 umbrellas-Delete temporary table Aban 2

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

The second method (recommended):

1. Sys or system permission is required first.

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, and the table name should be capitalized

3. Find out the order of all the 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 add for update directly after the statement in step 3 to modify it.

Finally, commit submits and restarts the Oracle service

The above is all the contents of the article "how to modify the order of fields in Oracle database tables". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.