The only other option is to do manually what many RDBMS systems do anyway…
– Create a new table
You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would cause a slow down, it’s not a lock…)
Once finished, change the name of the source table, then change the name of the new table. Preferably in a transaction.
Once finished, recompile any stored procedures, etc that use that table. The execution plans will likely no longer be valid.
Some comments have been made about this limitation being a bit poor. So I thought I’d put a new perspective on it to show why it’s how it is…
- Adding a new field is like changing one field on every row.
- Field Locks would be much harder than Row locks, never mind table locks.
- You’re actually changing the physical structure on the disk, every record moves.
- This really is like an UPDATE on the Whole table, but with more impact…