Applicable to 12c
Following post will help you to logically(not physical) reorder the columns in a table with the help of invisible columns feature in 12c.
Let's say, we have a table TAB1 having 2 columns COL1, COL2.
Now suppose that, we need to add new column COL3 and it should appear in middle of the table.. We could achieve this column reordering without using invisible columns as follows.
Following post will help you to logically(not physical) reorder the columns in a table with the help of invisible columns feature in 12c.
Let's say, we have a table TAB1 having 2 columns COL1, COL2.
CREATE TABLE TAB1
( COL1 NUMBER,
COL2 NUMBER);
Now suppose that, we need to add new column COL3 and it should appear in middle of the table.. We could achieve this column reordering without using invisible columns as follows.
- Rename the table TAB1 to something, For Ex: TAB2
- Add new column COL3 to table TAB2, which would appear at the end of the column list.
- Create a view or an editioning view named TAB1 that explicitly selects the columns in the right order
- Revoke the grants on TAB2 and issue those grants on the view TAB1
- Update the scripts(indexes, triggers, constraints) to refer renamed table TAB2
- First hide the column COL2 and Add column COL3. At this point, column COL3 will appear to be right after column COL1.
- Next unhide column COL2 and it will appear after column COL3 logically.
- Describe the table TAB1
ALTER TABLE TAB1 MODIFY COL2 INVISIBLE;
ALTER TABLE TAB1 ADD COL3 NUMBER;
ALTER TABLE TAB1 MODIFY COL2 VISIBLE;
DESC TAB1
Name Type
————— ——————
COL1 NUMBER
COL3 NUMBER
COL2 NUMBER