如何在多个用户（架构）中执行sql脚本 - 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.

我有一个拥有3个用户的oracle数据库（user1 / user1，user2 / user2，user3 / user3），我在3个用户中拥有相同数量/结构的表。

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.

我的问题是：当我想更新例如我在user1中的table1时，我想更新user2和user3中的同一个table1以保持更新，我想执行我的脚本一次，而无需登录其他用户并执行相同的脚本，因为我将有其他用户（4和5），这将花费大量时间在所有用户中执行一个脚本。

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

我想知道是否有一个工具或技术可以为多个用户执行一次脚本。

Thanks in advance.

提前致谢。

3 个解决方案

#1

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

您可以使用SQLPlus构建一个脚本，每个用户运行一次脚本。假设您有这样的脚本：

script.sql:

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:

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.

当然，这意味着您必须将密码存储在纯文本文件中，这可能存在安全风险。

#2

Try this if your script has only DML operations

如果您的脚本只有DML操作，请尝试此操作

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.

yourscript.SQL应该只包含由分割的DML命令;没有其他的。或者你可以修改它 - 创建一个过程有字符串参数。参数将是单个DML命令，它将在循环中为所有用户执行。

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

脚本yourscript.SQL没有限制，除了在DML操作中不使用模式名称作为前缀

P.S. Executing user should have enough rights.

附：执行用户应该有足够的权限。

#3

I would have a folder in which I have

我有一个文件夹，我有

the sql script
sql脚本
an OS script which connects to each database at a time and runs the script
一个OS脚本，它一次连接到每个数据库并运行脚本

要么

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

使用对所有其他3具有权限的用户运行它并对脚本进行个性化，以便为每个脚本运行。

#1