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?

Answers


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 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 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.


Need Your Help

Excel VBA, getting range from an inactive sheet

excel vba excel-vba

This script works fine when I'm viewing the "Temp" sheet. But when I'm in another sheet then the copy command fails.. It gives a "Application-defined or object-defined error"

C++ initialize const static vector dynamically

c++ vector static initialization const

I would like to initalize a static const std::vector in class Foo to {0, 1, 2, 3, ..., n} where n is known at compile time based on the value of Last in the enum below. The goal is for Foo::all to