How to reorder columns of table

If you want to change the order of the columns of any table without recreating it, then the following trick will work for you.

CREATE TABLE T_TEST
(
    C1 NUMBER,
    C2 NUMBER,
    C4 NUMBER,
    C6 NUMBER
);

Suppose you want to add two new columns, C3 and C5 without breaking the column order.

SELECT 
    COLUMN_NAME, COLUMN_ID 
FROM ALL_TAB_COLS 
WHERE TABLE_NAME = 'T_TEST' 
ORDER BY COLUMN_ID;
ALTER TABLE T_TEST MODIFY (C4 INVISIBLE, C6 INVISIBLE);

ALTER TABLE T_TEST ADD C3 NUMBER;

ALTER TABLE T_TEST MODIFY C4 VISIBLE;

ALTER TABLE T_TEST ADD C5 NUMBER;

ALTER TABLE T_TEST MODIFY C6 VISIBLE;
SELECT 
    COLUMN_NAME, COLUMN_ID 
FROM ALL_TAB_COLS 
WHERE TABLE_NAME = 'T_TEST' 
ORDER BY COLUMN_ID;

I hope it helps 🙂

Posted in SQL

One thought on “How to reorder columns of table

Leave a Reply

Your email address will not be published.