Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Question Unanswered: Alter NUMBER to NUMBER(10)

    Hello,
    I am trying to modify Oracle NUMBER column to NUMBER(10) without dropping the column and recreating

    e.g.:

    CREATE TABLE TEST(C1 NUMBER);

    INSERT INTO TEST(C1) VALUES (10);
    COMMIT;

    ALTER TABLE TEST MODIFY C1 NUMBER(10);

    I get the following error:

    SQL> ALTER TABLE TEST MODIFY C1 NUMBER(10);
    ALTER TABLE TEST MODIFY C1 NUMBER(10)
    *
    ERROR at line 1:
    ORA-01440: column to be modified must be empty to decrease precision or scale


    Any idea how can I do it without dropping the column/table and re-create ?

    TIA,

    Tal Olier (mailto:otal@mercury.co.il)

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    I guess your only option is to create a new table with the new schema, copy your data over and then drop the old one and rename the new one.

    Is this not possible ??

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    1st, thanks for answering,

    No this is not possible since I have 20 M records in this table and doing what you have suggested will take too long.

    Any other ideas ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    alter table test
    add column c2 number(10)

    update test set c2=c1

    alter table test
    drop column c1


    of course, oracle may not support DROP COLUMN, i'm too lazy to look that part up...


    rudy
    http://rudy.ca/

  5. #5
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    Thanks for the reply but I specifically have stated "without dropping the column and recreating"

    In Oracle 8.y.x, y>0 you can drop columns.





    Originally posted by r937
    alter table test
    add column c2 number(10)

    update test set c2=c1

    alter table test
    drop column c1


    of course, oracle may not support DROP COLUMN, i'm too lazy to look that part up...


    rudy
    http://rudy.ca/

  6. #6
    Join Date
    Aug 2002
    Location
    Monterey, CA
    Posts
    2
    Export the current table with compress=n
    truncate the table
    alter the column
    import the table with ignore=y

Posting Permissions

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