Hi All,
I have to update a specific table column by changing its datatype from number(3) to to alphanumeric ( i.e char(3) ). Apparently this change looks trivial however I have tried following methods for a live production system.
1) create a new table as original one but with new datatype for the affected field. then insert all records from old table to new table . table size is 200mb, the INSERT command took 1hr 17 mins to complete.
2) Performed following operation on the main table ( rather than creating a new one). Used the same table as used in step 1.
a) add the new field with modified column varchar2(10);
b) move the affected column data to new column
c) set original column value = NULL;
d) update original col datatype to varchar2(10)
e) move the new column data to original coumn
f) drop the new column;
This method also took almost 1.5 hrs to complete.
So, both of these methods look unacceptable from implementation point of view for a live system. There are around 12 tables to update , but one of them is very huge partitioned table of size 2.3 GB and used the field (LLF_CLASS) as a composite primary key. In other tables, LLF_CLASS field is not part of any key/constraint.
Is there any other method you can suggest which can reduce the time ?
Regards,
Subhasis