Modifying an Oracle number column

Question

I have a table where one of the columns is Number(22, 12) where is should be Number(22,2). Since it has data in it I can't just modify the column to the correct precision.

What would be the most efficient way to go about fixing the column?

One thing I didn't mention before is that the table has several indexes, triggers and is linked to other tables.

Answer 1

You could export the data, drop table, re-create and import or

Create a new table with the correct definition and then do:

INSERT /* +append */ INTO new_table (SELECT * FROM old_table);

drop the old table and then rename the new table to the old.

ALTER TABLE new_table RENAME TO old_table;

Answer 2

If you need to have the table online during the conversion and you have the required permissions to the database you can use DBMS_REDEFINITION. This link has more information. http://www.psoug.org/reference/dbms_redefinition.html.

If you don't need the table to be online, the easiest way is to add a column with the new datatype, copy the data over, and then drop the original column. If you need the column to be in the same order, you can create a view to the original table.

If you don't want to use a view, you can create a new table, copy the data over, drop the old table, then rename the new table.

Answer 3

ALTER TRIGGER trfoobar DISABLE;
ALTER TABLE foobar ADD (newcol number);
UPDATE foobar SET newcol=oldcol, oldcol=null;
ALTER TABLE foobar MODIFY(oldcol number(22,2));
UPDATE foobar SET oldcol=newcol;
ALTER TABLE foobar DROP(newcol);
ALTER TRIGGER trfoobar ENABLE;

Answer 4

I always try to avoid the rename table -> create new table -> do something to move data -> drop old table solutions if possible. There are always considerations of PL/SQL invalidations, recreating grants, locks, etc. that can complicate this.

If the trigger doesn't reference the column involved, I would:

• add the new column (obviously with a different name)
• update the table to set newColVal = oldColVal
• drop the old column
• rename the column to the old name

One minor drawback to this is that the new column will appear at the end of the table's column list when development tools describe the table - this has never bothered me but it does matter to some.

As Daniel E. pointed out, the DBMS_REDEFINITION package is an alternative to this, but in my experience it is time-consuming to get this set up and executing properly. It is worth learning this technique if you ever have to perform on-line changes to systems with absolutely no downtime.