Add column to a table for every shema in an oracle db 11g

The solution is dbms_redefinition package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.

What you have to do is:

• Create the interim table withe correct column definition
• Run redefinition
• Drop the old table

Use dbms_redefinition in the following way:

-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);   
END;
/

-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE 
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE', 
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');

If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:

• Execute privilege to DBMS_REDEFINITION
• Create any table
• Alter any table
• Drop any table
• Lock any table
• Select any table

Tables with the following characteristics cannot be redefined online:

• [9.0.1]Tables with no primary keys
• Tables that have materialized view logs defined on them
• [9i] Tables that are materialized view container tables and AQ tables
• [10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
• The overflow table of an IOT table
• Tables with fine-grained access control (row-level security)
• Tables with BFILE columns
• Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
• Tables in the SYS and SYSTEM schema
• Temporary tables

Other restrictions:

• A subset of rows in the table
• Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
• If new columns are being added with no column mappings, then they must not be declared NOT NULL until the redefinition is complete.
• There cannot be any referential constraints between the table being redefined and the interim table.
• Table redefinition cannot be done NOLOGGING.
• [10g] For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties.
• You cannot convert a nested table to a VARRAY.

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.