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 columns?
You can add, drop, and rename columns without issue.
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 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.
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.