How can I create a cronjob that runs a SQL script with removes old records?

I have an sql query which clears out records older than 45 days in oracle sql database.  I need to setup a cron job to run this sql query against 6 different database and have it run daily.  
Database Names:
db1
db2
db3
db4
db5
db6

Usernames are different for each database, but passwords are the same.
whoopwhoop2015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gheistCommented:
You can use oracle DBMS schedules
0
whoopwhoop2015Author Commented:
Need to go through ksh scripting to keep things standard unfortunately.    Need a ksh file to execute sql script for 6 different databases, which are all located on the same remote server.  Need to run sql script against one database first and once the first database is complete it will run the sql script against the remaining 5 database at the same time.
0
johnsoneSenior Oracle DBACommented:
Here is a shell of one of many methods that I could think of
#!/bin/ksh

#Set Oracle environment here.  Not sure how you do that.  Some people use oraenv.
#Remember that environment is not set in cron, you need to set it yourself.
#At a minimum, you need ORACLE_HOME set

common_pass=password

for db_user in db1/user1 db2/user2 db3/user3 db4/user4 db5/user5 db6/user6
do
  db=`echo $db_user | cut -f1 -d/`
  user=`echo $db_user | cut -f2 -d/`
  $ORACLE_HOME/bin/sqlplus ${user}/${common_pass}@${db} << EOF
select * from tab;
EOF
done

Open in new window

Since you said the password is the same for all, there is no need to have that in the pattern.  You need to put in the correct password for the common password and set the pairs of database and user correctly.  Then just add the commands after the sqlplus command.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gheistCommented:
It will be 10x faster if ran inside database.
t is way too many dependent parts, namely "remote" - all the network, and other machine that runs the scripts.
Consider at least running ksh script on DB server itself.
0
Gerwin Jansen, EE MVETopic Advisor Commented:
Once you have your script, here are some tips on how to schedule is as a crontab job:

http://www.experts-exchange.com/OS/Linux/A_17919-How-to-setup-a-crontab-job-in-Linux-Unix.html
0
johnsoneSenior Oracle DBACommented:
I'm having a hard time trying to figure out how a delete, which runs completely within the database and generates no return traffic, would run 10x faster through DBMS_JOB than through a SQL script.  Remotely or not.
0
whoopwhoop2015Author Commented:
Thank you johnsone, so something like this should work?  

#!/bin/ksh

#Set Oracle environment here.  Not sure how you do that.  Some people use oraenv.
#Remember that environment is not set in cron, you need to set it yourself.
#At a minimum, you need ORACLE_HOME set
common_pass=password

for db_user in db1/user1 db2/user2 db3/user3 db4/user4 db5/user5 db6/user6
do
  db=`echo $db_user | cut -f1 -d/`
  user=`echo $db_user | cut -f2 -d/`
  $ORACLE_HOME/bin/sqlplus -s ${user}/${common_pass}@(description=(address_list=(address=(protocol=TCP)(host=111.111.111.111)(port=1000)))(connect_data=(service_name=testservicename))) << EOF
"/test/testDelete.sql";
EOF
done

Open in new window

0
johnsoneSenior Oracle DBACommented:
I'm confused as to why you aren't using the tns name.  Are all the databases on the same machine?  That would go against your remote statement and I don't see where you are using different databases.

Also, within sqlplus, to run the script would be;

@/test/testDelete.sql

That would assume that is the correct absolute path to the script.
0
whoopwhoop2015Author Commented:
Two more questions and I will be done.  

How can I set a value like deleteDate at top of script and pass it into sql?  

I have another value I need to pass in that contains $/. like "fdasa$fdasf/.dfas" how can I have it ignore those characters for that variable and also pass that value in?  

#!/bin/ksh

#Set Oracle environment here.  Not sure how you do that.  Some people use oraenv.
#Remember that environment is not set in cron, you need to set it yourself.
#At a minimum, you need ORACLE_HOME set

common_pass=password
deleteDate = 80

for db_user in db1/user1 db2/user2 db3/user3 db4/user4 db5/user5 db6/user6
do
  db=`echo $db_user | cut -f1 -d/`
  user=`echo $db_user | cut -f2 -d/`
  $ORACLE_HOME/bin/sqlplus ${user}/${common_pass}@${db} << EOF
select * from tab where date = deleteDate;
EOF
done

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.