DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: process killed for table column addition

  1. #1
    Join Date
    Nov 2001
    Posts
    110

    process killed for table column addition

    A column was added to a 14 million row table with a default value. The processes was killed becuase it was taking resources/time. After the session was marked for kill, the table lost the primary key and 2 foreign keys. Any idea on how to find out how long the SMON process will take to clean up. Any possible explaination why the table dropped the primary key and a few constraints.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: process killed for table column addition

    Originally posted by Natik
    Any possible explaination why the table dropped the primary key and a few constraints.
    No, there's absolutely no reason for that. And I find it extremely strange if that is what realy happened. Are you sure that the addition of a new column and the lost of constraints are corelated? I highly doubt that, but if you are sure that's what realy happened you should report this to Oracle Support.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Possibly you could raise a TAR..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    My resolution when adding a column in a huge table (records/structure wise)is to split it into two process. 1st ALTER the table to add the column and then 2nd, UPDATE the table to populate the default value. And let the developer to write a TRIGGER to include the default value everytime there is a new record inserted. But that just me

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by reydp
    My resolution when adding a column in a huge table (records/structure wise)is to split it into two process. 1st ALTER the table to add the column and then 2nd, UPDATE the table to populate the default value. And let the developer to write a TRIGGER to include the default value everytime there is a new record inserted. But that just me
    The reason why I do this is that, as far as I can remember there was an error generated even after long hours of waiting. And I believe it was rollback segments related problem. I experimented with trying to issue first the SET TRANSACTION USE ROLLBACK SEG... before the ALTER TABLE ADD column with default value. But to my surprise, the same error was generated.
    So that's why I decided to split it into 2 process and inserted the SET TRANSACTION ... before the 2nd process was made and it works.


  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by reydp
    But to my surprise, the same error was generated.
    That's not surprising, that's an expected behavior. When you add a column with the dafault value to the table in one step, oracle actualy does it in three steps in the background:

    Step 1: ALTER TABLE t ADD COLUMN c DEFAULT NULL;
    Step 2: UPDATE t SET c = :default_value;
    Step 3: COMMIT;

    Now, step1 by itself is a transaction that has it's own implicit commit (sort of). So when you set the transaction to use a specific RBS before your ALTER TABLE ADD COLUMN command, that specific roollback segment was used only for step1. For step2 oracle immediately chooses another rollback segment. So in fact your effort to force the usage of a particular RBS has exactly the opposite effect - you was kind of preventing it from using the RBS of your choice for the rollback-intensive part of the operation.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by jmodic
    That's not surprising, that's an expected behavior. When you add a column with the dafault value to the table in one step, oracle actualy does it in three steps in the background:

    Step 1: ALTER TABLE t ADD COLUMN c DEFAULT NULL;
    Step 2: UPDATE t SET c = :default_value;
    Step 3: COMMIT;
    Yeahhh.. I forgot to mention that you have to explicitly issue a COMMIT statement after update, but you're right about the 3steps that oracle internally does when issuing an ALTER TABLE .. ADD COLUMN... DEFAULT ... I could have figure that out if i think more deeper when that problem camed.
    BTW, who DBAs that should be reminded that you need to do a COMMIT after INSERT,UPDATE,DELETE command?
    Just a thought....

    Originally posted by jmodic
    Now, step1 by itself is a transaction that has it's own implicit commit (sort of). So when you set the transaction to use a specific RBS before your ALTER TABLE ADD COLUMN command, that specific roollback segment was used only for step1. For step2 oracle immediately chooses another rollback segment. So in fact your effort to force the usage of a particular RBS has exactly the opposite effect - you was kind of preventing it from using the RBS of your choice for the rollback-intensive part of the operation.
    Amen to that.... Sometimes, when you do a lot heck of a works, issues that should have been known will also be overlooked. my bad....

  8. #8
    Join Date
    Oct 2002
    Posts
    807

    Re: process killed for table column addition

    Originally posted by Natik
    A column was added to a 14 million row table with a default value. The processes was killed becuase it was taking resources/time. After the session was marked for kill, the table lost the primary key and 2 foreign keys. Any idea on how to find out how long the SMON process will take to clean up. Any possible explaination why the table dropped the primary key and a few constraints.
    It might be worth using logminer to investigate, if you think that's what really happened.

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