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


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.


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:

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

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_%' );
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.