long running "alter table x drop unused columns checkpoint 10000;"
Mark_TNov 6 2012 — edited Nov 6 2012Hello,
I have an "alter table x drop unused columns checkpoint 10000;" that's been running for the past two and a half weeks. There is only one unused column in that table that is being dropped. The table has more than 14,725,000,000 records and according to dba_segments, is taking some 782 GB of storage (excluding indexes). Is there any way of knowing how far along the drop is or how many records are left to process? Are there any known bugs that I could be hitting (a search on MOS didn't find any, but it is possible that I might not be using the correct search terms).
Database is Oracle 11.2.0.1. The table is not partitioned. Nothing found in v$session_longops. No errors in alert.log. EVENT, P1, P1TEXT, P2, P2TEXT, P3, P3TEXT columns are chugging along in v$session.
Thank you,
Mark T.
P.S. Around a week after having submitted the ALTER TABLE command, my desktop PC crashed and the drop column command got interrupted. So I resumed the operation with "alter table x drop columns continue;". I'm pretty sure that has no effect on the time it takes for the operation to complete (the documentation states "Specify DROP COLUMNS CONTINUE to continue the drop column operation from the point at which it was interrupted."), but am including this info just in case.