Adding a column to an Oracle OLTP table

Question

I am trying to add a nullable column to a frequently used table in an Oracle 10 OLTP database when the application is running and busy. Adding a column with a zero value is only a change in the data dictionary, and therefore any table lock is retained only for a short period of time (which can be processed by the system).

The problem is that mine ALTER TABLEoften does not cope with this:

ORA-00054: resource busy and acquire with NOWAIT specified

My current approach is to change it by running it until there are locks in the table. This means that I cannot run such a script in SQL * Plus completely, but you need to copy and paste each statement and make sure that it works.

Is there a better way?

Answer 1

What about brute force approach? Put it in an endless loop and exit it when you're done. Pseudocode (did not check it):

create or replace 
procedure execDDL(ddl in varchar2) is
   myexp EXCEPTION;
   pragma exception_init (myexp, -54);
begin

 loop
   begin
      execute immediate ddl;
      exit;
   exception
      when myexp then 
         null;
 end loop;
 end;