Home » RDBMS Server » Server Administration » Effects of Alter Table ? (Linux SLES9)
Effects of Alter Table ? [message #357456] Wed, 05 November 2008 06:53 Go to next message
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 #357461 is a reply to message #357456] Wed, 05 November 2008 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the table data is currently modified, you can't alter the table.
If it is not, you can alter it and others can't modify it or its data.

In short, ALTER TABLE takes an exclusive lock on the table.

Regards
Michel
Re: Effects of Alter Table ? [message #357464 is a reply to message #357456] Wed, 05 November 2008 07:41 Go to previous messageGo to next message
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
Michel Cadot
Messages: 64120
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
Previous Topic: SYS password doesn't work
Next Topic: Oracle Restore Point
Goto Forum:
  


Current Time: Tue Dec 06 16:21:32 CST 2016

Total time taken to generate the page: 0.08861 seconds