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.
what if I want to reorder the existing column with dropping table and updating columns
For example if I want to put C6 at ID 2
Existing :
Column_name Order_Id
C1 1
C2 2
C3 3
C4 4
C5 5
C6 6
Required
Column_name Order_Id
C1 1
C6 2
C2 3
C3 4
C4 5
C5 6
any solution for it ?
You can reorder with the same logic.
SQL> create table t_test
2 (
3 C1 number,
4 C2 number,
5 C3 number,
6 C4 number,
7 C5 number,
8 C6 number
9 );
Table created.
SQL> insert into t_test(c1, c2, c3, c4, c5, c6) values(1, 2, 3, 4, 5, 6);
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t_test modify (c2 invisible, c3 invisible, c4 invisible, c5 invisible);
Table altered.
SQL> alter table t_test modify (c2 visible, c3 visible, c4 visible, c5 visible);
Table altered.
SQL> select * from t_test;
C1 C6 C2 C3 C4 C5
———- ———- ———- ———- ———- ———-
1 6 2 3 4 5
SQL> column column_name format a10
SQL> select column_name, column_id from all_tab_cols where table_name = ‘T_TEST’ order by column_id;
COLUMN_NAM COLUMN_ID
———- ———-
C1 1
C6 2
C2 3
C3 4
C4 5
C5 6
6 rows selected.
Thank you, this helped!