Unix/Linux Go Back    


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

UNIX for Advanced & Expert Users


Tags
solved

Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 1 Week Ago   -   Original Discussion by ATWC
ATWC's Unix or Linux Image
ATWC ATWC is offline
Registered User
 
Join Date: Jan 2014
Last Activity: 11 January 2018, 9:49 AM EST
Posts: 36
Thanks: 2
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
    #2  
Old Unix and Linux 1 Week Ago   -   Original Discussion by ATWC
jgt's Unix or Linux Image
jgt jgt is offline Forum Advisor  
Registered User
 
Join Date: Apr 2007
Last Activity: 20 January 2018, 8:19 AM EST
Location: 44.21.48N 80.50.15W
Posts: 2,029
Thanks: 2
Thanked 278 Times in 260 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; 1 Week Ago at 07:41 AM..
Sponsored Links
    #3  
Old Unix and Linux 1 Week Ago   -   Original Discussion by ATWC
Yoda's Unix or Linux Image
Yoda Yoda is offline Forum Advisor  
Jedi Master
 
Join Date: Jan 2012
Last Activity: 20 January 2018, 12:42 PM EST
Location: Galactic Empire
Posts: 3,639
Thanks: 257
Thanked 1,323 Times in 1,240 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; 1 Week Ago at 03:14 PM.. Reason: input redirection fix, thanks to RudiC for pointing that out.
    #4  
Old Unix and Linux 1 Week Ago   -   Original Discussion by ATWC
MadeInGermany's Unix or Linux Image
MadeInGermany MadeInGermany is offline Forum Staff  
Moderator
 
Join Date: May 2012
Last Activity: 20 January 2018, 1:50 PM EST
Location: Simplicity
Posts: 3,944
Thanks: 334
Thanked 1,319 Times in 1,189 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 (1 Week Ago), Yoda (1 Week Ago)
Sponsored Links
    #5  
Old Unix and Linux 1 Week Ago   -   Original Discussion by ATWC
ATWC's Unix or Linux Image
ATWC ATWC is offline
Registered User
 
Join Date: Jan 2014
Last Activity: 11 January 2018, 9:49 AM EST
Posts: 36
Thanks: 2
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
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux 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 08:59 PM
how to run who am i from remote session raidzero UNIX for Dummies Questions & Answers 2 08-25-2008 03:20 PM
sqlplus session being able to see unix variables session within a script 435 Gavea Shell Programming and Scripting 2 07-03-2006 11:11 AM
Session time out newtoxinu UNIX for Dummies Questions & Answers 2 07-27-2005 12:43 PM



All times are GMT -4. The time now is 04:39 PM.