UNIX for Advanced & Expert Users

Expert-to-Expert. Learn advanced UNIX, UNIX commands, Linux, Operating Systems, System Administration, Programming, Shell, Shell Scripts, Solaris, Linux, HP-UX, AIX, OS X, BSD.

Run only 3 sqlplus session at a time


👤 Login to reply

ATWC ATWC is offline
Registered User
 
Join Date: Jan 2014
Last Activity: 9 February 2018, 4:19 PM EST
Posts: 39
Thanks: 3
Thanked 0 Times in 0 Posts
Run only 3 sqlplus session at a time

Below code runs n number of session at a time when called. I want to restrict it to run only for 3 sqlplus at a time. Is there a way to do the same.
Code:
for name in `cat abc.txt`
do
(sqlplus -s  usert/password@host <<-EOF>> error.txt
			SET LINESIZE 1000
			select '$name', TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as start_time from dual;
			EXEC proc('$name');
			select '$name' ,TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as end_time from dual;
			EOF
 ) &
done
wait

run the below part only if all the loops in the for has completed!
echo "Complete"

Sponsored Links
jgt's Unix or Linux Image
jgt jgt is offline Forum Advisor  
Registered User
 
Join Date: Apr 2007
Last Activity: 21 July 2018, 11:07 AM EDT
Location: 44.21.48N 80.50.15W
Posts: 2,081
Thanks: 2
Thanked 296 Times in 271 Posts
Code:
count=0
for name in `cat abc.txt` do (sqlplus -s  usert/password@host <<-EOF>> error.txt
             SET LINESIZE 1000
             select '$name', TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as start_time from dual;
             EXEC proc('$name');
             select '$name' ,TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as end_time from dual;
             EOF  ) &
let count=$count+1
if [ $count -gt 2 ]
then
   wait
   count=0
fi
 done 
wait 
run the below part only if all the loops in the for has completed! echo "Complete"


Last edited by rbatte1; 01-09-2018 at 06:41 AM..
Sponsored Links
Yoda's Unix or Linux Image
Yoda Yoda is offline Forum Advisor  
Jedi Master
 
Join Date: Jan 2012
Last Activity: 21 July 2018, 5:07 PM EDT
Location: Galactic Empire
Posts: 3,671
Thanks: 257
Thanked 1,331 Times in 1,247 Posts
That for loop is basically wrong: Useless Use of Backticks

The right way is to use a while loop.

Here is another approach using pgrep to count the number of child sqlplus
Code:
#!/bin/ksh

while read name
do
        sqlplus -s usert/password@host <<EOF &
        set linesize 1000
        SELECT '$name', To_char(CURRENT_TIMESTAMP(0),'DD-Mon-YYYY HH24:MI:SS') AS start_time FROM dual;
        EXEC PROC ('$name');
        SELECT '$name', To_char(CURRENT_TIMESTAMP(0),'DD-Mon-YYYY HH24:MI:SS') AS end_time FROM dual;
        exit
EOF
        [ "$( pgrep -P $$ -l sqlplus | wc -l )" -eq 3 ] && wait

done < abc.txt


Last edited by Yoda; 01-08-2018 at 02:14 PM.. Reason: input redirection fix, thanks to RudiC for pointing that out.
MadeInGermany MadeInGermany is offline Forum Staff  
Moderator
 
Join Date: May 2012
Last Activity: 21 July 2018, 8:10 PM EDT
Location: Simplicity
Posts: 4,156
Thanks: 365
Thanked 1,419 Times in 1,275 Posts
The wait waits for all jobs to finish.
If your jobs have a different run time and you want to keep the running jobs near the allowed maximum, then count them with the jobs command
Code:
parallel=3
while read name
do
  echo "running $name"
  ( 
    sqlplus ...
  ) &
  while [ `jobs | wc -l` -ge $parallel ]; do  sleep 2; done
done < abc.txt

The Following 2 Users Say Thank You to MadeInGermany For This Useful Post:
rbatte1 (01-09-2018), Yoda (01-08-2018)
Sponsored Links
ATWC ATWC is offline
Registered User
 
Join Date: Jan 2014
Last Activity: 9 February 2018, 4:19 PM EST
Posts: 39
Thanks: 3
Thanked 0 Times in 0 Posts
Code:
sqlplus.sh
---------

sqlplus -s  usert/password@host <<-EOF>> error.txt
			SET LINESIZE 1000
			select '$name', TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as start_time from dual;
			EXEC proc('$name');
			select '$name' ,TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as end_time from dual;
			EOF
			
--------------			
while read name
do
(sh sqlplus.sh) &
	while [ $(jobs -l | wc -l) -ge 3 ]
	do
		echo "";
		sleep 120;
	done
done < abc.txt
wait

echo "Complete"

By doing the above check condition it makes sure only 2 process runs at a time and will wait for the specified time given and then check again and process it.
Sponsored Links
👤 Login to reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
How to run scripts within a telnet session? esolve Shell Programming and Scripting 1 12-19-2012 07:59 PM
how to run who am i from remote session raidzero UNIX for Dummies Questions & Answers 2 08-25-2008 02:20 PM
sqlplus session being able to see unix variables session within a script 435 Gavea Shell Programming and Scripting 2 07-03-2006 10:11 AM
Session time out newtoxinu UNIX for Dummies Questions & Answers 2 07-27-2005 11:43 AM



All times are GMT -4. The time now is 12:52 AM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
×
UNIX.COM Login
Username:
Password:  
Show Password





Not a Forum Member?
Forgot Password?