DBA Tips for Changing Column Order in Oracle!!

Author: Robert Jeffry

Most of us have been working for years with Oracle but sometimes it happen that we get stuck in minor things and feel frustrated when have to spend time in making a simple logic. Today we will discuss a simple scenario that most of you must have faced one time or another.

Sometimes we face the scenario where we need to change the order of columns in an existing table. If our table is quite large say consisting of several millions rows then it would be a headache to re-create the table and then migrate the data.

Well here is a simple solution to this problem that will certainly easy your task.

CREATE TABLE MYTABLE
(
  MYCOL 4 NUMBER,
  MYCOL 3 NUMBER,
  MYCOL 2 NUMBER,
  MYCOL 5 NUMBER,
  MYCOL 1 NUMBER,
  MYCOL 6 NUMBER
);

The SELECT * command will retrieve result set based on the order of columns as they are created.

SELECT * FROM MYTABLE ;

In order to retrieve results in desired order you will be required to write column names.

SELECT MYCOL1, MYCOL2, MYCOL3, MYCOL4, MYCOL5, MYCOL6 FROM MYTABLE ;

You can also create a view for it.

CREATE OR REPLACE VIEW MYTABLE_VIEW
AS
   SELECT MYCOL1, MYCOL2, MYCOL3, MYCOL4, MYCOL5, MYCOL6
   FROM MYTABLE ;

Now you just need to SELECT * from the view and then clean up the view.

SELECT * FROM MYTABLE_VIEW ;

DROP VIEW MYTABLE_VIEW ;

I then changed the name of the table and created the view so that it looks like the original table.

RENAME MYTABLE TO ORIG_ MYTABLE ;

CREATE VIEW MYTABLE
AS
   SELECT MYCOL1, MYCOL2, MYCOL3, MYCOL4, MYCOL5, MYCOL6
   FROM ORIG_ MYTABLE ;

Now the view will retrieve the results exactly as desired by you.

SELECT * FROM MYTABLE ;

You can insert into table with no problem at all.

INSERT INTO MYTABLE ( MYCOL1, MYCOL2, MYCOL3, MYCOL4, MYCOL5, MYCOL6 ) VALUES (1,2,3,4,5,6);
COMMIT; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Secrets of Truly Dropping a Table in Oracle 10g!!
   DBA Tips: Handling Recordsets in Oracle Procedures!!
   Efficiently handle problematic Oracle Sequences!!
   Oracle Security Guide: Is your Database Secure?