Oracle – How to Apply Operation on All Schemas

oracleschemaupdate

I have an Oracle instance, with several schemas (all starting with the same prefix). 

PATT_A
PATT_B
PATT_C
...

All of them have the same table, and I'd like to update all of them in one shot (with SYSTEM account).

I know I can get these schemas doing so :

SELECT username FROM dba_users WHERE username LIKE 'PATT_%' ;

And I would like to apply the same query on them, like this one :

UPDATE table1 SET field1 = 'new_value' WHERE field2 = 'filter' ;

I assume something like a loop could do the job, but I don't know how to use it here.

Best Answer

You can do it with Native dynamic SQL ( http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011 ) using the EXECUTE IMMEDIATE statement. Here's an example:

DECLARE
 v_sql varchar2(128);
 v_username varchar(30);
 cursor c1 is
    select username from dba_users where username like ('PATT%');
BEGIN
    for REC in c1 loop
        v_sql := 'UPDATE ' || REC.username || '.table1 SET field1 = ''new_value'' WHERE field2 = ''filter'''  ;
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/

Otherwise, you can make a script with each needed statement with a simple query like

SELECT '''UPDATE ' || username || '.table1 SET field1 = ''new_value'' WHERE field2 = ''filter'';' from dba_users where username in ( 'PATT_%' );
Related Question