Shulou(Shulou.com)06/01 Report--

This article to share with you is about Oracle database how to modify the order of the fields in the table, Xiaobian think it is very practical, so share it for everyone to learn, I hope you can gain something after reading this article, not much to say, follow Xiaobian to see it.

preface

Some time ago, someone asked me a question about the order adjustment 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 default will put the field at the end of the table, and there are many fields and we want to put the associated fields together, then we have to modify the field order. Before we change the order, let's look at how fields can be added to Oracle database tables.

New fields:

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

examples

Create table structure:

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

Add a field:

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

Add three fields simultaneously using a single SQL statement:

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

field order modification

The first original method:

Delete reconstruction, although this method is simple and crude, but not scientific

--Create a temporary table to store the correct order create table A_2 as select (column1, colon 2,... order in table A) from A_1 ;--Delete table A_1 drop table A_1;--Create A_1 and assign it the correct order and value from table A_2 create table A_1 as select * from A_2;--Delete temporary table A_2 drop table A_2;

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

The second method (recommended):

First, you need sys or system permissions.

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 user, TEST is table to be changed, table name should be capitalized

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

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

4. Order of modification

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 and restart Oracle services

The above is how Oracle database modifies the order of fields in the table. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please 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.