如何遍历不同的模式并在每个模式上执行一些 sql? [英] How to loop through different schemas and execute some sql on each?

问题描述

我有一个案例，我有 70 个 oracle 模式，我必须在每个模式上执行相同的脚本

I have a case where I have 70 oracle schemas and I have to execute the same script on each

这是实现这一目标的最佳方式.

which would be the best way to achieve this.

可以使用 CURSOR 吗?

Is it possible with a CURSOR?

现在我用

ALTER SESSION SET current_schema = SCHEMA_1;
====
ALTER SESSION SET current_schema = SCHEMA_2;
====
ALTER SESSION SET current_schema = SCHEMA_3;
====

我用我的脚本替换了===="，我用 Notepad++ 来做，但我必须手动准备脚本，如果脚本很长，我必须把它分成多个块而不用换行和为每个块做一个替换

And I replace the "====" with my script, I+m doing it with Notepad++ but I have to prepare the script manually and if the script is long I have to split it in multiple chunks without new lines and do a replace for each chunk

我想更自动化一点.

推荐答案

我提供以下半自动方式，它不会自动执行您的任务，而是减少了搜索和替换.

I offer the following semi-automatic way, which does not automate your task, but cuts down on the search and replace.

如果您使用的是 SQL*Plus，则可以使用以下语法执行文件:

If you are using SQL*Plus, you can execute a file with the following syntax:

@myscriptfile.sql

如果您想为每个模式执行一次，您可以通过查询字典生成代码:

If you would want to do that once for each schema, you could generate the code by querying the dictionary:

select 'ALTER SESSION SET current_schema = ' || owner || ';
       @myscriptfile.sql'
  from dba_users
 where <your filter>;

然后您只需将该查询的结果复制/粘贴到 sqlplus 中即可.可能可以将其假脱机以归档并执行它.

Then you would just copy/paste the result of that query in sqlplus. It is probably possible to spool that to file and execute it.

这篇关于如何遍历不同的模式并在每个模式上执行一些 sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！