How to connect multiple users/schemas in Oracle 11g?
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&gt; create user user1 identified by user1;
User created.
SQL&gt; grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

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

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

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

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

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

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

SQL&gt; connect user2/user2;
Connected.
SQL&gt; 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&gt; create user user1 identified by user1;
User created.
SQL&gt; create user user2 identified by user2;
User created.
SQL&gt; create user admin identified by admin;
User created.

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

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

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

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

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