Alter Physical Structure of table Oracle11gr2

Question

I have a table

Name              Age                    RollNo.
A                  1                      10
B                  2                      20

Now I want to alter the table permanently in such a way that,After altering it should look as below

RollNo.           Name                    Age
10                 A                      1
20                 B                      2

How shall i alter this table , All i want to do is to change physical structure of the table.

I think not. why do you want to alter it? you can always rearrange them through `SELECT` statements — John Woo, Jan 10 '13 at 06:19
Yes but in Database i want to alter this table. With select i know i can alter it. @JW — Akki, Jan 10 '13 at 06:20
Yes it is the simplest way i have seen but can you please help me with altering physical structure of the table @tammy — Akki, Jan 10 '13 at 06:29
well, is it possible to create a new table? If yes then create a new table with the correct positioning and create a script which could fetch the existing data from the old table into the new table. — tam tam, Jan 10 '13 at 06:36
Unfortunately Oracle only allows columns to be added to the end of an existing table or creating a view is pretty much what I could think of. — tam tam, Jan 10 '13 at 06:45
@tammy its difficult creating new table because the table has many other dependencies such as triggers,procedures — Akki, Jan 10 '13 at 16:56
The order of columns in a table is irrelevant. There is no need to do it. — , Jan 10 '13 at 17:22

Branko Dimitrijevic · Answer 1 · 2013-01-11T11:59:10.220

3

Why do you want to do it?

If it's just because you'd like to have a correct order of columns when using SELECT *, then you should not have used * in the first place. Always use the exact list of columns in your queries.

If it's because you think it would improve the performance, have you done the actual measurements? I doubt you'll find many scenarios where changing the physical column order influences performance in a significant way. There are some scenarios with chained rows where it might (see the "Row Chaining" section in this article), but that doesn't apply to narrow rows such as yours.

That being said, you could:

CREATE TABLE NEW_TABLE AS SELECT <different column order> FROM OLD_TABLE.
Recreate all the relevant constraints (such as keys, FKs), indexes and triggers/procedures on the NEW_TABLE.
DROP TABLE OLD_TABLE.
ALTER TABLE NEW_TABLE RENAME TO OLD_TABLE.

You might also want to look at the dbms_redefinition if you need to do that while accepting updates.

edited Jan 11 '13 at 11:59

answered Jan 11 '13 at 11:23

Branko Dimitrijevic

50,809
10
93
167

I am not viewing it by typing query such as Select. I am using Oracle11gr2 where I can directly view the table graphically in SQL Developer @Branko Dimitrijevic – Akki Jan 11 '13 at 17:41
1

@Akki Then just rearrange the columns in the SQL Developer UI: either drag-and-drop column headers, or use the _Columns..._ option from the context-menu (right-click on column headers). BTW, this is not related to any particular Oracle database version - this is purely a client-side operation. – Branko Dimitrijevic Jan 11 '13 at 21:46
Yes by dragging and dropping column header i am able to view it in the order i need thanks @Branko Dimitrijevic – Akki Jan 12 '13 at 10:23

score 0 · Answer 2 · edited May 23 '17 at 11:51

You can drop and create the table without loosing the data in oracle using statement

create table YOUR_TABLE_BU as select * from YOUR_TABLE

Please go through the link - How can I create a copy of an Oracle table without copying the data? for more details. Try:

CREATE TABLE YOUR_TABLE_BU AS SELECT * FROM YOUR_TABLE;

DROP TABLE YOUR_TABLE;

CREATE TABLE YOUR_TABLE AS SELECT RollNo., Name, Age FROM YOUR_TABLE_BU;

DROP TABLE YOUR_TABLE_BU;

Alter Physical Structure of table Oracle11gr2

2 Answers2