How to easily reorder columns in a table

Y

You might be frustrated when you want to add a column to a table and you can't choose a specific position. You can't easily change column order either. Well, this is the best trick. Just change the position in dictionary without moving data, cloning or recreating a table. Or without using DBMS_REDEFINITION.

Move specific column to the end of the table:

DECLARE
    in_table_name       CONSTANT user_tab_cols.table_name%TYPE      := '&TABLE_NAME';
    in_column_name      CONSTANT user_tab_cols.column_name%TYPE     := '&COLUMN_NAME';
BEGIN
    EXECUTE IMMEDIATE
        'ALTER TABLE ' || in_table_name || ' MODIFY ' || in_column_name || ' INVISIBLE';
    --
    EXECUTE IMMEDIATE
        'ALTER TABLE ' || in_table_name || ' MODIFY ' || in_column_name || ' VISIBLE';
END;
/

To reorder all columns in desired order you need to provide a full list of colums. Here is how to get the current list:

SELECT LISTAGG(c.column_name, ',') WITHIN GROUP (ORDER BY c.column_id) AS columns
FROM user_tab_cols c
WHERE c.table_name = '&TABLE_NAME';

You can export data from this query for all tables in one environment and apply it on another env to keep tables same.

You manually alter this list according to your needs and pass it to this script:

DECLARE
    in_table_name       CONSTANT user_tables.table_name%TYPE    := '&TABLE_NAME';
    in_columns          CONSTANT VARCHAR2(32767)                := '&COLUMNS';      -- COLUMN_NAME,COLUMN_NAME,COLUMN_NAME...
BEGIN
    FOR c IN (
        SELECT c.column_name
        FROM user_tab_cols c
        LEFT JOIN user_constraints n
            ON n.table_name             = c.table_name
            AND n.constraint_type       = 'P'                       -- skip primary key columns
        LEFT JOIN user_cons_columns l
            ON l.table_name             = c.table_name
            AND l.column_name           = c.column_name
            AND l.constraint_name       = n.constraint_name
        WHERE c.table_name              = in_table_name
            AND l.constraint_name       IS NULL
        ORDER BY c.column_id DESC
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || in_table_name || ' MODIFY ' || c.column_name || ' INVISIBLE';
        EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -54039 THEN  -- you need at least 1 column visible
                NULL;
            ELSE
                RAISE;
            END IF;
        END;
    END LOOP;
    --
    FOR c IN (
        SELECT d.column_name
        FROM (
            SELECT
                REGEXP_REPLACE(REGEXP_SUBSTR(in_columns, '[^,]+', 1, LEVEL), '\s+', '') AS column_name,
                LEVEL                                                               AS column_id
            FROM DUAL
            CONNECT BY REGEXP_SUBSTR(in_columns, '[^,]+', 1, LEVEL) IS NOT NULL
        ) d
        JOIN user_tab_cols c
            ON c.table_name         = in_table_name
            AND c.column_name       = d.column_name
        ORDER BY d.column_id
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || in_table_name || ' MODIFY ' || c.column_name || ' VISIBLE';
        --
        DBMS_OUTPUT.PUT_LINE(c.column_name);
    END LOOP;
END;
/

Check invalid objects, recompile if needed and you are done.