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

3 thoughts on “How to reorder columns of table

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *