After ‘drop unused columns’ table in not usable state. Ora-600 [17016]

After ‘drop unused columns’ table in not usable state. Ora-600 [17016]

In a datawarehouse environment (11.1.0.7 on RedHat) with compressed tablespaces, tried to drop some columns in the following way:

alter table DWH.TEST_TABLE set unused column COLUMN_SIX;
alter table DWH.TEST_TABLE drop unused columns;

This took a very long time, let it run for 24 hours (!), noticed that the undo-tablespace (80GB…) completely was filled up. Then I killed the command. No penalty, everything was back to normal.
So my intention was to use the addition ‘checkpoint 100000’ , and came across a very old but good explanation at Tom’s site here.

(more…)

X