(Oracle): Columns in partially dropped state

Question

I use Oracle 10g. Since months I have the following error regarding a table:

ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

The statement ALTER TABLE DROP COLUMNS CONTINUE fails for overtime.

I have no DBA privileges on this database.

What could I do? Drop & recreate the table?

It's a massive table with million of records.

What I tried:

1. Once upon a time, I made the following command to set some columns in unused state:

   ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
   

   Then, I gave the following command:

   ALTER TABLE hr.admin DROP UNUSED columns;
   

   The system hangs up, the operation is too long, so it faults.

   Now the table hr.admin has two columns in partially dropped state, and I can't go neither forward, nor backward.

   I don't understand why this happened.

2. I made the following steps, the system hangs up at STAGE TWO:

   STAGE ONE ============

   SQL> select * from user_unused_col_tabs;
   
   TABLE_NAME  COUNT
   ----------- ----------
   TEMP        1
   

   STAGE TWO ============

   SQL> alter table temp drop unused columns;
   
   Table altered.
   

   STAGE THREE =============

   SQL> select * from user_unused_col_tabs;
   
   no rows selected
   

3. Checkpoint 500 option

   I am trying again with the following statement:

   ALTER TABLE MYUSER.MYTABLE DROP COLUMNS CONTINUE CHECKPOINT 500;
   

   Could the CHECKPOINT 500 option help me?

Answer 1

We have given for about twelve consecutive times the command:

ALTER TABLE MYUSER.MYTABLE DROP COLUMNS CONTINUE CHECKPOINT 250;

The statement was automatically killed every 48 hours, this is the reason because we had to launch it several times.

About 500 hours of elaboration to definitely drop the columns in partially dropped state...!!

It is confirmed that CHECKPOINT 250 makes a "commit", so at the next launch of the same command, you start from the point of stop.