Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Angry Unanswered: Column type change in a Huge table

    Hi
    I have a table that has close to 20 million records. On this table one of the column needs to be changed from number to char.
    I am doing the following to make the column change:

    - create a bakup table
    - delete the values from the column in question of the original table
    - change the column to char
    - update the column from the backup table using the primary key

    Now I get the following problems:
    1. The table gets locked
    3. Server gets loaded and locks up the database
    2. The data might get currupted due to hanged session
    Now what would be the best way to make this change smootly and without loading up the server and locking up the database.

  2. #2
    Join Date
    Oct 2004
    Posts
    4

    Lightbulb

    do this.
    suppose you have a table emp of 20 mil rows. and you want to change column desk_id ( char(5) ) from char to number. follow these steps.

    If your business rule on the column allows then disable all triggers on your table. drop indexes which are using your target column. recreate them after you finish these steps.

    1) create table empbak (myrowid, desk_id) as select rowid, to_number(desk_id) from emp;

    2) alter table emp drop column desk_id;

    3) alter table emp add desk_id number(5);

    4) update emp a set a.desk_id = (select b.desk_id from empbak b where a.rowid = b.myrowid);

    hope this will help you.

    regards,
    Abhijit

Posting Permissions

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