Effects of Alter Table ? [message #357456] |
Wed, 05 November 2008 06:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
Where can I find more information about the effects of an alter table ?
I am specifically interested in the effects the user will experience - will the table be locked so they can work, etc.
TIA
Dirk
|
|
|
|
Re: Effects of Alter Table ? [message #357464 is a reply to message #357456] |
Wed, 05 November 2008 07:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dirkm
Messages: 86 Registered: November 2008 Location: Centurion - South Africa
|
Member |
|
|
Thank you Michel.
Another question.
I see that some alter tables are very quick, and others take very long to run. I am guessing some make the change somewhere in the dictionary (not physically on the table ?), and other make the change on the table (rebuilds every row of the table ?).
I need to understand this better, to know which alter tables I can run during the day, and which after hours (those that will take long).
Is it the alter table that physically changes a table (increase (or even decrease) a column length, or alter a column type for example), that takes long ?
I hop this makes sense (trying to describe it as best I can).
regards
Dirk
|
|
|
Re: Effects of Alter Table ? [message #357467 is a reply to message #357464] |
Wed, 05 November 2008 08:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68679 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
increase: just update in dictionary
decrease: it depends on your version, in 9.2 and up it is possible if no data is greater than the new size, so it can be long. Before it can't be done if there are some data in the table, otherwise it is just update in dictionary, so short.
change type: can't be done if the table is not empty, if it is it is just an update in the dictionary.
Regards
Michel
|
|
|