Oracle: Adding, renaming, and dropping columns on live database

What operations are permitted and not permitted on a live, running Oracle database? Specifically, how is this handled when adding, dropping, and renaming column开发者_运维技巧s?

---

You can make almost all changes like this on a running instance. Of course I wouldn't recommend doing this with USERS in the system.

That said, Oracle 11.2 has a killer new feature called EBR, Edition-Based-Redefinition. Think of it like being able to "commit" and "rollback" DDL changes. That might be useful, depending upon circumstance.

---

Online changes (ie while other sessions are active) can be done using DBMS_REDEFINITION (described here, here and here) but that is an Enterprise Edition feature

---

You can add, drop, and rename columns without issue.

Caveats,

adding a column with a default value forces the database to issue an update statement for existing rows. Be careful on large tables or the load will spike.

rename a column can break your app. Plan for this but otherwise you should be fine. Also realize that you will be generating errors for queries that try to access the column unless you plan things properly.

---

you can do those things. if the database finds a reason to disallow the change, you will get a nice nastygram with a cool error code you can check for what you need to do next.

you will have less trouble if there are no other users runnign queries or no jobs running in the background tc.