-
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
-
Why don't you just modify the table to reduce the size:
alter table xxx modify (yyyy varchar2(zz))
You can do this if the table is empty. It it isn't:
a) copy the data to a temporary table (using create table as select)
b) truncate the table
c) alter the column
d) copy the data back.
-
If you are using Designer and these columns will always have the same datatype, you can create a datatype and then just modify that datatype.
For example, if you had many columns in your db for lastname as a VARCHAR2(40) and wanted to change to VARCHAR2(50) you would just change the LASTNAME type if all of the columns were defined as such.
It makes changes to the ddl easy, but you still need to load data into a temp table, drop the object and reload.
-
Are you familiar with DBMS_REDEFINITION (9i)?
-
Perhaps I wasn't clear enough, they are number columns, and I an increasing the column width, not decreasing, which you supposed to be able to do even if there is data in the table.
Whats funny is, that when I create a temp table as a copy, truncate the data in it, then change the columns from a number(3) to number(5,3) so I can add 3 decimal points to the numbers, it then lets me insert as select all of the data from the original table.
But if I try to change the table with data in it from number(3) to number(5,3) it wont let me, but it will let me change it to number(6,3) with the data in the table.
I think while wrting this I may realize why now, because the original column width was number(3), and all the data in it was no more than 2 numbers, oracle must still think there is 3 numbers in the columns therefore wont let me change it to 5,3 but will allow 6,3. But when I insert as select it works because all of the data is a max of 2 numbers... strange!
