How to execute sql script in multiple users (schema)

---

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.

---

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

PS Executing user should have enough rights.

---

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.

链接地址: http://www.djcxy.com/p/95056.html

上一篇: C ++

下一篇: 如何在多个用户中执行sql脚本（模式）