Rename and drop table columns

If I have a relatively big table and I want to modify a column from one type to another, i.e. number to varchar2(50), I may experience very long time. Note if I just change, for example, from varchar2(10) to varchar2(50), it will not be a problem, Oracle can finish in a split of second.This was what I faced last night during the code release. The column was acutally added in the previous release, but for some reason it has not been used and is containing no data. The problem was that I would run out of the maintenance window if I Ire just waiting for it to complete (obsevered it would take 4-5 hours from v$session_longops view).

In this situation, the capability of renaming a column name comes to help. I renamed the column with a suffix '_old', and added a new column with the desired data type and name. It completed in seconds.

The syntax looks like:

SQL> alter table myschema.mytab rename column MYCOL to MYCOL_old;
SQL> alter table myschema.mytab add MYCOL varchar2(50);


It is noted in the Oracle online doc that when you rename a column, Oracle updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.

So now comes to the question what is the best way to drop those *_old columns? - There are actually 4 tables with 8 columns I addressed in this way.

Checking the online document again, there is a section in the Admin Guide (9i) that covers some options. I did not see I can do parallel drop of a column. I did notice there is an interesting option: checkpoint, which I may want to consider as our tables are very large. For example:

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;


The doc describes:
 
" This option causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space."