Can not alter table 1

[Dachyon]

Hi SQL Experts,
I have been trying to alter a table, to add an extra column, but everytime I try to ALTER TABLE, the query goes off into no-mans land, and just keeps processing for up to 10 minutes, until I cancel it.

I have tried dropping the constraints, even dropping the table itself, but that just results in the same thing. There are only 56 records in the table!

I have rebooted the server, without any affect.
Can anyone HELP !

Many Thanks.
D

 

[ousoonerjoe]

Have you set the new column to allow for nulls and/or give the new column a default value?

Have you committed your changes? When doing updates i have forgotten my commit trans and it causes the same effect you describe.

I have found it to be much simpler to add columns through the Enterprise Manager since it will take care of all the issues i have mentioned here. (Or at least give you an error telling you what you've missed.)

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Crowley16]

what about any locks on that table?

--------------------
Procrastinate Now!

 

[SQLSister]

ousoonerjoe, I would not recommend ever adding columns to tables in Enterprise Manager. If you have large tables you will lock up the system for quite some time doing this as Enterprise Manager will actually create a new table, move the data, drop the old table and rename the new one. Alter table in QA does not do that and so is much faster and less resource intensive.

Dachyon, please provide your alter table code.

"NOTHING is more important in a database than integrity." ESquared

 

[ousoonerjoe]

I was not aware of the over head, Sister. Thank you for the tip.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.