-
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 Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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"
-
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.
-
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?
-
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....
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|