I need to change the column precision in about 8 tables from a number (x) to number (x,3).

What is the best / safest way to go about this?

The tables have one or two primary keys, and some have referential constraints as well.

I am not decreasing the precision, but it still wont let me alter the table as there is data in it.

Most of the tables have minimal data in them, excpet one with about 500,000 rows.

Do I:

create table table1_temp as select * from table1;
truncate table table1;
alter table table1 modify column_name number(3,3);
insert into table1 select * from table1_temp;
drop table1_temp;

Or do I:

rename table table1 to table1_temp;

--modify then run the DDL from table1_temp to reflect the new column precision and original table name

insert into table1 select * from table1_temp;
drop table table1_temp;

Or is there a better way that I havent thought of?


thanks in advance