Drop Column
Hi,
We have a table of size above 500GB. To satisfy some business need there was a requirement to drop one column. The column needed to be dropped contained huge data. Hence before dropping the column it was made unused and then we tried to drop the same. At this point we are facing some issues. The drop unused column has been executed before 18 hours and till now it is not completed.
Suspecting some discrepancy the session conducting the drop command has been killed. Now the table could be described but we are not able to select the table. I am providing the steps followed so far.
First the column was set to unused:
SQL> alter table table_name set unused (column_name);
At some later time the drop command had been executed:
SQL> alter table table_name drop unused columns checkpoint 1000;
The above statement was running for last 18 hours and till now not completed.
Then the corresponding session was killed.
After this the table could be described but select operation could not be performed and returned the following error:
ORA-12986:
columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
The above command was also executed but without any sign of completion and again killed.
DB Version: 10.2.0.3.0
OS: Sun Solaris 10
Environment: DWH
Kindly guide me at this point on how to resolve this and made the table available to the user.
Thanks in advance for your valuable time.
Regards,
Joy