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 🙂
Thanks.