How to insert a column in a specific position in oracle without dropping and recreating the table?

by

Amit-

I don’t believe you can add a column anywhere but at the end of the table once the table is created. One solution might be to try this:

CREATE TABLE MY_TEMP_TABLE AS
SELECT *
FROM TABLE_TO_CHANGE;

Drop the table you want to add columns to:

DROP TABLE TABLE_TO_CHANGE;

It’s at the point you could rebuild the existing table from scratch adding in the columns where you wish.
Let’s assume for this exercise you want to add the columns named “COL2 and COL3”.

Now insert the data back into the new table: 

INSERT INTO TABLE_TO_CHANGE (COL1, COL2, COL3, COL4) 
SELECT COL1, 'Foo', 'Bar', COL4
FROM MY_TEMP_TABLE;

When the data is inserted into your “new-old” table, you can drop the temp table.

DROP TABLE MY_TEMP_TABLE;

This is often what I do when I want to add columns in a specific location. Obviously if this is a production on-line system, then it’s probably not practical, but just one potential idea.

-CJ

More Related Contents:

  1. Maximum Number of times we can query an oracle database [closed]
  2. How to uninstall / completely remove Oracle 11g (client)?
  3. Oracle trigger after insert or delete
  4. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  5. How to handle Day Light Saving in Oracle database
  6. What is the effect of placing the commit after DML in procedure?
  7. Auto Increment for Oracle
  8. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  9. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  10. Oracle Search all tables all columns for string [duplicate]
  11. NLS_NUMERIC_CHARACTERS setting for decimal
  12. creating parameterized views in oracle11g
  13. Create Pivot view in SQL from a SQL table
  14. How to create a user in Oracle 11g and grant permissions
  15. Table-Valued Functions in ORACLE 11g ? ( parameterized views )
  16. Trigger to enforce M-M relationship
  17. How can I describe a table in Oracle without using the DESCRIBE command?
  18. Functions vs procedures in Oracle
  19. Get the number of days between two dates in Oracle, inclusive of the dates
  20. HikariCP: What database level timeouts should be considered to set maxLifetime for Oracle 11g
  21. passing in table name as plsql parameter
  22. How to use parameters in a ‘where value in…’ clause?
  23. Why cannot I create triggers on objects owned by SYS?
  24. No more data to read from socket
  25. return resultset from function
  26. ORA-12704: character set mismatch
  27. BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed
  28. How can I get the number of days between 2 dates in Oracle 11g? [duplicate]
  29. Autoincrement in oracle to already created table
  30. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00

Leave a Comment