CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
James
James

Reputation: 1456

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

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.

Upvotes: 2

Views: 6905

Answers (3)

Jgrammer
Jgrammer

Reputation: 326

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.

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 3

Jucan
Jucan

Reputation: 421

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.

Upvotes: 0

Related Questions