Estimate the remaining time of drop column operation

If table size is huge it may take long time to drop a column and even if there are multiple columns dropped together it may multiply the time with the number of columns in drop operation.

There is an interesting article written by Jonathan on the same and good to read.

http://allthingsoracle.com/dropping-columns/

Following are my personal observations and interesting to see different-2 behavior.

1. Drop column directly.

ALTER TABLE t_big drop column SKIP_CORRUPT;

ALTER TABLE t_big drop (SKIP_CORRUPT,MAX_EXTENTS);

If we drop column directly the table is locked exclusively while the drop is executing and the entire drop is executed as a single massive transaction. That may increase redo logs drastically and if we do not have enough size ,this operation may fail.


If we are using this option then we can monitor time remaining by using v$session_longops  or alternatively by using TOAD session browser/long ops.

12 28 % 13-05-2016 01:04:38 AM 175 Rowid Range Scan:  SCPOMGR.T_BIG: 34000 out of 123044 Blocks done 67

2. By using checkpoint If we do not want REDO to grow drastically and improve performance of drop operation, we can use check point with frequency of commit intervals. alter table t_big drop column MIN_EXTENTS checkpoint 5000; But drawback with checkpoint is if operation fails due to some reason or  terminated by user(I did that simply by CTRL+C when drop column operation was running on sqlplus) then it leaves entire table into table in unusable state where we cannot perform any operation on the table. If we try to perform any operation on table it will through below error. [Error] Execution (1: 15): ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE To overcome this we need to execute command as suggested in error.  But If we are using this option then I observed that we can't monitor time remaining by using v$session_longops  or alternatively by using TOAD session browser/long ops. If checkpoint is used then long operations information is not available in v$session_longops. Same in the case of below statement.  ALTER TABLE t_big DROP COLUMNS CONTINUE.  I assume that oracle usage checkpoint for this operation as well. 3. Set column unused and drop latter. (alternatively in 12C we can mark column invisible also if the purpose is to hide columns from application only) ALTER TABLE t_big SET UNUSED COLUMN NEXT_EXTENT ; alter table T_BIG drop unused columns checkpoint 5000; Interestingly even if column marked as unused but still column still exists and it places lock on table when you actually drop this column like the normal drop operation. In this case also it depends if we can monitor time or not based on the checkpoint usage. Also if checkpoint used and operation failed due to any reason, it leaves table in unusable state. Footnote: Be careful when using checkpoint or be ready for the consequences .