Home » Other » General » Tool for executing oracle scripts
Tool for executing oracle scripts [message #482948] Wed, 17 November 2010 01:18 Go to next message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Hi,

Is there any tool which can be used to execute a oracle scripts in multiple schema.

we are having nearly 75 schema and need to execute a script in all 75 schema. it is really time consuming job to do. there are possibility of lot of errors in this case.

we have tried some batch mode concept. but we find some difficulties on this. am planning to create a application in VB which can do this job.

but we wanted to check on the net before we do something on our own and we do not want to reinvent the wheel again.

Thanks
Re: Tool for executing oracle scripts [message #482949 is a reply to message #482948] Wed, 17 November 2010 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68258
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your main concern.
If it is just execute a SQL script in many schemas, SQL*Plus can well do it.
If it is to execute it in parallel, you can do it very simply do it with a shell script.

Regards
Michel
Re: Tool for executing oracle scripts [message #482969 is a reply to message #482948] Wed, 17 November 2010 03:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10703
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If the script is generic, all you need is to change / pass the schema name.
If it errors in sql*plus, it will error in any tool.
Re: Tool for executing oracle scripts [message #483027 is a reply to message #482969] Wed, 17 November 2010 11:43 Go to previous messageGo to next message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Hi Michel and Mahesh,

thansk for the reply.

It is not parallel. Instead of a person executing the scripts manually every schema one by one, I would like some tool to do. Errors in the meaning, i could miss some scripts while copying and pasting in different schema or i could miss a schema. this was i meant by errors.

I do not know about shell script. let me see how i can use it in this case.

thank you once again.
Re: Tool for executing oracle scripts [message #483028 is a reply to message #483027] Wed, 17 November 2010 12:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10703
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
In the most simplest form,
You need something like this.
someScript connects to database identified by $1 (first input) and
executes the queries.
You can call scripts (.sql files) too.
$DBA AND $DBP will be my username/password stored elsewhere.
You can either prompt it and read the values
or
store the info separately and loop through each value.

Depending on what you want exactly, you can automate this any fancier.

Kaapi:ora magvivek$ ./someScript apex

'THISISSOMESQL'
---------------
this is somesql


'THISISSOMEOTHERSQL'
--------------------
this is someothersql

Kaapi:ora magvivek$ cat someScript
sqlplus -s $DBA/$DBP@$1 <<EOF
select 'this is somesql' from dual;
select 'this is someothersql' from dual;
exit;
EOF

[Updated on: Wed, 17 November 2010 12:03]

Report message to a moderator

Re: Tool for executing oracle scripts [message #483071 is a reply to message #483028] Thu, 18 November 2010 01:56 Go to previous message
Michel Cadot
Messages: 68258
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends of what contains your script.
Here's how you can do.
My example script is the following one:
select sysdate from dual
/
select sys_context('userenv','current_schema') from dual
/

It is located in "C:\t.sql".

First, with a DBA account, dynamically create a script that will call the target script for each user. My user list are all users starting with 'M', yours can be a list in your own table or in a file... For each user, I set the current schema to this schema, start a spool containing the account name and call the target script:
set echo off
set head off
set feed off
set linesize 2000 
set trimspool on
spool c:\t_exec.sql
select 'alter session set current_schema='||username||';'||'
spool t_'||username||'
start '||'c:\t.sql
spool off'
from dba_users
where username like 'M%'
order by 1
/
spool off
set head on 
set feed on
set termout on

I name this script "c:\t_gen.sql", I execute it:
SQL> @c:\t_gen.sql

Now the generated script "c:\t_exec.sql" contains the instruction to call the target script in the schema of each user:
alter session set current_schema=MDSYS;
spool t_MDSYS
start c:\t.sql
spool off
alter session set current_schema=MESDVD$LECTEUR;
spool t_MESDVD$LECTEUR
start c:\t.sql
spool off
alter session set current_schema=MESDVD$PROPRIO;
spool t_MESDVD$PROPRIO
start c:\t.sql
spool off
alter session set current_schema=MESDVD$TOUT;
spool t_MESDVD$TOUT
start c:\t.sql
spool off
alter session set current_schema=MGMT_VIEW;
spool t_MGMT_VIEW
start c:\t.sql
spool off
alter session set current_schema=MICHEL;
spool t_MICHEL
start c:\t.sql
spool off

So I can call it to execute what I want in all the selected schemas and I get the result for each user in a spool named "t_<theuser>.lst":
SQL> @c:\t_exec.sql

Session altered.

SYSDATE
-------------------
18/11/2010 08:48:38

1 row selected.

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------------
MDSYS

1 row selected.


Session altered.

SYSDATE
-------------------
18/11/2010 08:48:38

1 row selected.

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------------
MESDVD$LECTEUR

1 row selected.


Session altered.

SYSDATE
-------------------
18/11/2010 08:48:38

1 row selected.

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------------
MESDVD$PROPRIO

1 row selected.


Session altered.

SYSDATE
-------------------
18/11/2010 08:48:38

1 row selected.

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------------
MESDVD$TOUT

1 row selected.


Session altered.

SYSDATE
-------------------
18/11/2010 08:48:38

1 row selected.

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------------
MGMT_VIEW

1 row selected.


Session altered.

SYSDATE
-------------------
18/11/2010 08:48:38

1 row selected.

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------------
MICHEL

1 row selected.

SQL> host dir E:\Temp\Listing\t_*.lst
 Le volume dans le lecteur E s'appelle Data
 Le numéro de série du volume est 4A9F-7C49

 Répertoire de E:\Temp\Listing

18/11/2010  08:48               399 t_MDSYS.LST
18/11/2010  08:48               408 t_MESDVD$LECTEUR.LST
18/11/2010  08:48               408 t_MESDVD$PROPRIO.LST
18/11/2010  08:48               405 t_MESDVD$TOUT.LST
18/11/2010  08:48               403 t_MGMT_VIEW.LST
18/11/2010  08:48               400 t_MICHEL.LST

"E:\Temp\Listing" is the default directory where my SQL*Plus output goes. Now, the results are in each of these files, for instance:
SQL> host type E:\Temp\Listing\t_MICHEL.LST
SYSDATE
-------------------
18/11/2010 08:48:38

1 row selected.

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
-------------------------------------------
MICHEL

1 row selected.

There I do it manually to show you the principles but all the steps can be done in one script call changing the last line of "t_gen.sql" ("set termout on") to the following one:
@c:\t_exec.sql

You have no more the output on the screen and the generated script is automatically called.
The only thing you have to do is to write this "t_gen.sql" which is generic and can be easily customize.

Regards
Michel


[Updated on: Thu, 18 November 2010 01:59]

Report message to a moderator

Previous Topic: specified index does not exist though it is exist
Next Topic: Oracle/SQL + website
Goto Forum:
  


Current Time: Sun Aug 14 04:05:17 CDT 2022