Recently i had to set up a db for an App and wanted to test the how better the server scaled when there are “n” number of connections at a time. I did it on a Windows 2003 server. Below is the script which i wrote for this.
On Windows
make_conn.bat
——————
FOR %%A in (1 2 3) DO start sqlplus userid/passwd@tns_entry @conn.sql
conn.sql
———–
select sysdate from dual; exec dbms_lock.sleep(10); exit;
Save both the files in the same folder with respective names.
Now double click the make_conn.bat and this would create 3 connections to the db and will exceute the conn.sql in each. The contents of the conn.sql are self explanatory. To make more connections all you need to do is to add more numerics to the FOR statement. Changing the make_conn.bat to (1 2 3 1 2 3 1 2 3 4 5 6) would create 12 connections and the more numbers you add, more threads(connections) are created.
If you want to trigger the connections from cmd.exe then you will need to modify the FOR statement like :
FOR %A in (1 2 3) DO start sqlplus userid/passwd@burp @conn.sql
Note that there is only one % symbol.
On Unix/Linux
make_conn.sh
—————–
#!/bin/ksh
#
#Make n number of connections to db
#
#
echo "Enter the number of connections to make :\c"
read NOF;
i=0
while [[ $i -lt $NOF ]];
do
sqlplus -s userid/passwd @conn.sql >/dev/null &
i=$i+1
done
exit;
conn.sql
——–
select sysdate from dual; exec dbms_lock.sleep(10); exit; $chmod 755 make_conn.sh $make_conn.sh
The “&” in the while loop will run the process in background and will direct the o/p to /dev/null, so you will not see any message in your terminal unless there is an error.
In both cases, you can test your number of connections by
select count(8) from v$session where username=’USERID’;
