| Tool for executing oracle scripts [message #482948] |
Wed, 17 November 2010 01:18  |
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 #483027 is a reply to message #482969] |
Wed, 17 November 2010 11:43  |
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 |
 |
Mahesh Rajendran
Messages: 10708
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 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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:
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:
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
