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 01-08-2018   -   Original Discussion by ATWC
ATWC's Unix or Linux Image
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
    #2  
Old Unix and Linux 01-08-2018   -   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 May 2018, 7:12 PM EDT
Location: 44.21.48N 80.50.15W
Posts: 2,066
Thanks: 2
Thanked 287 Times in 264 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
    #3  
Old Unix and Linux 01-08-2018   -   Original Discussion by ATWC
Yoda's Unix or Linux Image
Yoda Yoda is offline Forum Advisor  
Jedi Master
 
Join Date: Jan 2012
Last Activity: 10 May 2018, 11:06 AM EDT
Location: Galactic Empire
Posts: 3,670
Thanks: 257
Thanked 1,330 Times in 1,246 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.
    #4  
Old Unix and Linux 01-08-2018   -   Original Discussion by ATWC
MadeInGermany's Unix or Linux Image
MadeInGermany MadeInGermany is offline Forum Staff  
Moderator
 
Join Date: May 2012
Last Activity: 20 May 2018, 6:10 PM EDT
Location: Simplicity
Posts: 4,076
Thanks: 351
Thanked 1,374 Times in 1,238 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
    #5  
Old Unix and Linux 01-09-2018   -   Original Discussion by ATWC
ATWC's Unix or Linux Image
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
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 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:56 AM.