CODEWITHSUNDEEP
oracle-databasesessionschemaalter
simonC
simonC

Reputation: 4327

How to loop through different schemas and execute some sql on each?

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.

Is it possible with a CURSOR?

For now I'm doing it with 

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

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 would like to automate this a little bit more.

Upvotes: 3

Views: 3340

Answers (1)

Ronnis
Ronnis

Reputation: 12833

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

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>;

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.

Upvotes: 4

Related Questions