Avatar of Mushfique Khan
Mushfique Khan
 asked on

Unix scripting: need some help

Need to run sql scripts via unix/linux, against corresonding databases, basically updating some columns in different databases/schema & tables too, have all the update scripts ready, but rightnow they are all individual scripts, need to merge them all together as one shell script, in order for them to be executed as sys and globally too, basically just after the refresh job completed, they'll execute this one global scripts, which'll update all the columns in the corresponding databases/schemas & tables too.

Can any one please assist/advise/guide in writing this unix shell script, here is the req:

- should be only one script for all the databases/environments
- must be executed as sys, because can't code the password, will be kicked off by the job schedular, just after the refresh job finished.
- not all the columns are same in all the dbs, means the script need to check the databases running on this box/server and then only execute the script corresponding to that database/schema & tables only.

Also is there any way to commit in between too, because just executed one script and there is big spike on the db, means would like to commit too, in between, after some thousands of updates.

Can any one please assit/guide here ... thanks in advance.
Oracle DatabaseShell ScriptingUnix OS

Avatar of undefined
Last Comment
MikeOM_DBA

8/22/2022 - Mon
johnsone

Connecting as SYS is extremely dangerous.  If you are subject to any type of auditing, I'm sure that isn't allowed.  Creating a OS authenticated account that only have the privileges required to do what the script does would be a much more secure way to handle it.  No password and no elevated privileges would be required.

Not sure exactly how this is working.  I'm assuming that you are going to deploy one script on many machines.  One large script to maintain.

If that is the case, I would put the commands for each database into a single if block.  Then pass in the databases that are on the machine on the command line.  That can be maintained in the job scheduler as databases don't move often.

Something like ....

if [ $1 == "db1" ] ; then
  export ORACLE_SID=db1
  sqlplus / << EOF
     .....
     commit;
     exit
EOF
fi

if [ $1 == "db2" ] ; then
  export ORACLE_SID=db2
  sqlplus / << EOF
     .....
     commit;
     exit
EOF
fi

Open in new window


That is a basic idea of how I would do it.  You probably need to add more if there are multiple databases on a machine and maybe some error processing.
ASKER CERTIFIED SOLUTION
MikeOM_DBA

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy