Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59

    Unanswered: Oracle Column Sequence

    Hello All,
    Can I change the sequence of column in oracle table?
    If yes how?
    I know it is very silly question but..........
    Cyrus

  2. #2
    Join Date
    Sep 2003
    Posts
    16

    Re: Oracle Column Sequence

    I take it you mean you have a table like:-

    MyTable(field1, field2, field3)

    and you want MyTable (field1, field3, field2)?

    You can create a new table based on a query, then drop the old one:-

    create table MyTable2 as select field1, field3, field2 from MyTable;

    drop table MyTable;

    create table MyTable as select * from MyTable2;

    drop table MyTable2;

  3. #3
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    Ya the example u stated is write.
    I know that i can drop and again create it but I have a lots of data and so i don't want to drop it..........

    cyrus

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If performance and downtime is an issue try the following mods to Paul's suggestion

    1.create table MyTable 2 as select ....

    2. Add any constraints such as PK etc.

    3.rename MyTable to MyTable_old

    4.rename MyTable2 to MyTable

    5.truncate table MyTable_old

    6.drop table MyTable_old

    7.take a backup.

    This should work with hardly any downtime but you will have to make sure data is not modified during the process. It is more complicated if you have foreign key constraints though : ).

    If you have 9i r2 then you might also want to look at dbms_redefinition if it is a production environment.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •