How to execute sql script in multiple users (schema) - ORACLE

Question

i have an oracle Database with 3 users (user1/user1 , user2/user2, user3/user3) and i have the same number/structure of tables in the 3 users.

my issue is : when i want to update for example my table1 in the user1 , i want to update the same table1 in the user2 and user3 in order to keep them updated , i want to execute my script once , without login to the other users and execute the same script because i will have an other users (4 and 5 ) and this will take a lot of time to execute one script in the all users.

i'm wondering if there is a tool or technique to execute an script once for multiple users.

Thanks in advance.

Aleksej · Accepted Answer · 2017-01-18 12:08:09Z

3

You can use SQLPlus to build a script that runs your script once per user. Say you have a script like this:

script.sql:

select count(1) from obj;

and you want to run it for two users; you can build a script like the following:

scriptLauncher.sql:

conn alek/****@xe
@d:\script
conn hr/****@xe
@d:\script

The result:

SQL> @d:\scriptLauncher
Connected.

  COUNT(1)
----------
        15

Connected.

  COUNT(1)
----------
        35

Of course this means that you have to store your passwords in a plain text fle, which may be a security risk to take in consideration.

edited Jan 18, 2017 at 12:08

answered Jan 18, 2017 at 11:55

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kris Johnston Over a year ago

In addition to this answer, consider granting proxy access to the various users to a single user. Connect to each proxy user using that single user like the following: conn alek[hr]/****@xe. Finally, parameterize the password for that single user. This should avoid the security concerns.

Jgrammer · Accepted Answer · 2017-01-20 11:36:33Z

Try this if your script has only DML operations

SET SERVEROUTPUT ON
DECLARE
   TYPE my_users_type IS VARRAY (5) OF VARCHAR (256);
   my_users   my_users_type;      
BEGIN
   my_users := my_users_type ('USER1', 'USER2', 'USER3');         --your users
   FOR i IN my_users.FIRST .. my_users.LAST   LOOP
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =' || my_users (i);
      @D:\yourscript.SQL; --your script with path
  END LOOP;
end;
/

yourscript.SQL should contain only DML commands splitted by ; Nothing else. Or you can modify it - create a procedure has string parameter. Parameter will be single DML command which will be executed in loop for all users.

or just use

ALTER SESSION SET CURRENT_SCHEMA = USER1
@D:\yourscript.SQL;
ALTER SESSION SET CURRENT_SCHEMA = USER2
@D:\yourscript.SQL;
ALTER SESSION SET CURRENT_SCHEMA = USER3
@D:\yourscript.SQL;

No limitations to your script yourscript.SQL, except don't use schema names as prefixes in your DML operations

P.S. Executing user should have enough rights.

Jucan · Accepted Answer · 2017-01-18 11:53:31Z

0

I would have a folder in which I have

the sql script
an OS script which connects to each database at a time and runs the script

OR

Run it with a user which has rights over all the other 3 and personalize the script so it runs for each.

answered Jan 18, 2017 at 11:53

Jucan

4214 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to execute sql script in multiple users (schema) - ORACLE

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related