To add a column to an oracle(8i) table someplace other than the end of the list?

pts.
Tags:
Oracle
SQL
Greetings, Is there a way to add a column to an oracle(8i) table someplace other than the end of the list? Thank you very much, Paula Chen

Answer Wiki

Thanks. We'll let you know when a new response is added.

One way (maybe the only way) would be to recreate the table. You could do that by first creating the new table with the columns in the order you want them. Then insert the rows of the old table into the new table (using “Insert into table2 (col1,col2..) select col1,col2.. from table1″). Then rename or drop the old table. Then rename the new table. You will need to recreate the indexes, permissions, contraints, and triggers as well.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Redwine
    Paula, It can be done but, could be time consuming depending on your table size. Save the table data into a temporary table, drop the old table, create the new table with the desired column order, then insert into the new table the data from the temporary table. This also allows for recovery from the temporary table. INSERT INTO EMP_TEMP (temporary table) SELECT * FROM EMP (old table); DROP TABLE EMP (old table); Create the new table. CREATE TABLE EMP ( COL1 COL2 (NEW COLUMN) COL3 COL4 ) STORAGE clause; INSERT INTO EMP SELECT COL1, COL3, COL4 FROM TEMP_EMP;
    20 pointsBadges:
    report
  • Paulachen
    Thank you all for the valuable advices. Is there a way to reorder the columns without drop/recreate the Oracle table? Thank you, Paula
    0 pointsBadges:
    report
  • Randym
    I am not aware of any other way. Redwine says it can be done, maybe he/she will reply with the way. Can I ask why it is important to reorder the columns? If it is because you want to just do "select * from table" in sql*plus or something like that and see the columns in a certain order, maybe you could create a view with the columns in the correct order and then select * from the view.
    1,740 pointsBadges:
    report
  • Paulachen
    Randy, The reason I need to reorder the table is there is a existing standard in the department I am working for that the table columns have to be in proper order, such as Primary Key first. Thank you very much, Paula
    0 pointsBadges:
    report
  • Paulachen
    Randy, The reason I need to reorder the table is there is a existing standard in the department I am working for that the table columns have to be in proper order, such as Primary Key first. Thank you very much, Paula
    0 pointsBadges:
    report
  • Bit4man
    1) you shouldn't be using Oracle8i anymore. We don't use Win98 either. 2) The order of data in the table really is insignificant. It can be argued, that if you only select the first 5 out of 50 columns, that Oracle can skip reading 45 columns from the buffer and save time; and while that saves a little bit of CPU, it doesn't save IO - the whole block is read from disk. Putting NULL columns at the end of the table is a potential space saver - as trailing null columns does not get anything written to tbe block. Nulls in between data does have a marker byte or two. 3) Resequencing the columns in the table means the order in witch the columns are stored is changed. This means re-creation of the table. For a lot of reasons. There are options of dropping columns, and then adding them again at the end. In THEORY, you can then duplicate the columns you want in the right order, and then drop them. But you'll have to rebuild all your constraints again - and your block usage simply will be wasted. I would predict a ton of chained rows if you try that, in which case you would end up having to recreate your tables again. 4) You can automate the re-creation. Or even better, don't change the tables - just use views that show the columns in a different order. In a good secure system, nobody accesses the tables directly anyway. Using views you can hide technical needed columns that might just confuse. You can add basic business logic so your applications don't have to repeat it. However, if you really insist, once the views are created with the new order, create the tables in a new schema based on the views. That's easily done with a simple script (create table ... as select ..). Then do an export where you don't export data, and do an import with ignore=yes on the new schema. That should recreate your constraints - assuming that the column names are the same. If the number of changes are high, such as column names changing, constraints and table names are changing - and you're forced to make these fundamental changes (i really would disagree that it's the right appraoch) - I wouldn't try to ALTER my way out of it. I would create a migration script that table by table, created the new schema and then migrated the data between them. In short- no, there's no ALTER TABLE MOVE COLUMN ... but there's a ALTER TABLE DROP COLUMN and ALTER TABLE ADD that adds NULL columns at the end. As written above, it's really not worth your while to preseve the internal object id for the table, if you have huge changes. Use views in the future, and cosmec changes like this are easily managed.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following