If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

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.

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 ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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

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.

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 ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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:

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....

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.