Alter column data type in production database

Question

I'm looking for the best way to change a data type of a column in a populated table. Oracle only allows changing of data type in colums with null values.

My solution, so far, is a PLSQL statement which stores the data of the column to be modified in a collection, alters the table and then iterates over the collection, restoring the original data with data type converted.

-- Before: my_table ( id NUMBER, my_value VARCHAR2(255))
-- After: my_table (id NUMBER, my_value NUMBER)

DECLARE
  TYPE record_type IS RECORD ( id NUMBER, my_value VARCHAR2(255));
  TYPE nested_type IS TABLE OF record_type;
  foo nested_type;
BEGIN
  SELECT id, my_value BULK COLLECT INTO foo FROM my_table;
  UPDATE my_table SET my_value = NULL;
  EXECUTE IMMEDIATE 'ALTER TABLE my_table MODIFY my_value NUMBER';
  FOR i IN foo.FIRST .. foo.LAST
  LOOP
    UPDATE my_table 
        SET  = TO_NUMBER(foo(i).my_value) 
        WHERE my_table.id = foo(i).id;
  END LOOP;
END;
/

I'm looking for a more experienced way to do that.

Answer 1

The solution is wrong. The alter table statement does an implicit commit. So the solution has the following problems:

• You cannot rollback after alter the alter table statement and if the database crashes after the alter table statement you will loose data
• Between the select and the update users can make changes to the data

Instead you should have a look at oracle online redefinition.

Answer 2

Your solution looks a bit dangerous to me. Loading the values into a collection and subsequently deleting them fom the table means that these values are now only available in memory. If something goes wrong they are lost.

The proper procedure is:

1. Add a column of the correct type to the table.
2. Copy the values to the new column.
3. Drop the old column.
4. Rename the new column to the old columns name.