3 Replies Latest reply: May 20, 2010 8:19 AM by riedelme RSS

    Adding column in specific position


      I have a table with 6 columns. Now I want to add a column between the 3rd and the 4th column. I'm using Oracle 10g. Is there anyway to do this? I don't want to drop and recreate the table.

        • 1. Re: Adding column in specific position

          No you cannot so that...but why you want to do this when you can select columns from the table in any order irrespective of order they are stored in the table.

          • 2. Re: Adding column in specific position
            Just create a view with the columns in the correct order and you are set. Remember databases are about sets, they are not about records. In sets the order of tuples and attributes is unimportant.

            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: Adding column in specific position
              As NKU and Sysbrand pointed out there's no real need to to put a new column in a specific place. Relational theory allows for the columns to be anywhere in a table row so the order should not matter.

              If you must put the column in a specific place you will have to do a lot of work. Back up the old table for safety. Create a new table with the new column where you want it. Copy the old data to the new table. Recreate any indexes, constraints, etc. Drop the old table. Rename the new table to the old name. Drop the backup if everything is okay after testing.

              Easier just to add the column to the end of the table.