best way to change column precision?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: best way to change column precision?

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82

    Question

    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

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185

    Unhappy

    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.
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Are you familiar with DBMS_REDEFINITION (9i)?

  5. #5
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82

    Exclamation

    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!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width