What happens in Oracle when I drop a column?

Question

What are the various things that happen when I drop a column from a filled table. In all_tab_columns, does the column_id of other columns get reset?

Answer 1

alter table drop column

will actually visit each block and remove the column data - and that's an expensive thing to do for a large table.

You might find it more prudent to issue:

alter table set unused

which just flags the column as "gone" in the dictionary. If you then still need to reclaim that space, you can schedule an "alter table drop unused" at a quiet time

Answer 2

The actions will include at least the following:

• The data stored in that column is lost.
• Views referencing that column are invalidated - but (according to Gary - thanks!) they're not dropped; they stay invalid until revised to work with the modified schema.
• Stored procedures referencing that column are invalidated - same caveat.
• The column ID numbers of following columns will be reset.
• Permissions granted on the column will be removed.
• Indexes referencing that column will be dropped.