Script to kill session

[tom quach_1]

dear all,
Please help me with this script.

i am trying to write a script to terminate locking sessions and it run from cron every 5 mins.
if column 6 and 7 are 6 and 6 using AWK to get value in column 1 and 2 and then insert it into sql statement "alter session....."
if no line is returned then exist.

#!/usr/bin/sh

export NLS_LANG=AMERICAN_AMERICA.UTF8
/u10/app/oracle/product/db10g/bin/sqlplus / as sysdba <@/home/oracle/ORACLE/CHK/locktest.sql
EOF
grep 6 /home/oracle/ORACLE/CHK/wlm.log|awk '{if ($6==6 && $7==6) print}'
if no line return exist (Please help on this syntax)
##if column 6 and 7 of file wlm.log are 6 and 6 then
else
awk '{print $1 " " $2}'


250 13233
251 13233

--Do you know away to insert 2 results back to this sql statement

/u10/app/oracle/product/db10g/bin/sqlplus / as sysdba <ALTER SYSTEM KILL SESSION '&sid,&serial' ;



/home/oracle/ORACLE/CHK/locktest.sql
this is the result of locktest.sql

wlm.log
SID SERIAL# TY ID1 ID2 LMODE REQ
----- ---------- -- --------- --------- ------
764 1 TS 3 1 3 0
250 13233 TM 79026 0 3 0
250 13233 TX 196627 3532464 6 0
250 13233 TX 196627 3532464 6 6
251 13233 TX 196627 353246 6 6


Thanks in advance,
Tom

[Dennis Handly]

>if column 6 and 7 are 6 and 6 using awk to get value in column 1 and 2 and then insert it into sql statement "alter session....."
>Do you know away to insert 2 results back to this sql statement

Try this:
awk '
($6==6 && $7==6) {
printf "ALTER SYSTEM KILL SESSION '%s,%s' ;\n", $1, $2
}' /home/oracle/ORACLE/CHK/wlm.log > sql.cmd

if [ ! -s sql.cmd ]; then
exit
fi

/u10/app/oracle/product/db10g/bin/sqlplus / as sysdba < sql.cmd

I'm not sure of the exact format in SQL. Did you want a "&" before the numbers?

[tom quach_1]

Thanks Dennis,


Try this:
awk '
($6==6 && $7==6) {
printf "ALTER SYSTEM KILL SESSION '%s,%s' ;\n", $1, $2
}' /home/oracle/ORACLE/CHK/wlm.log > sql.sql

if [ ! -s sql.sql ]; then
exit
fi

/u10/app/oracle/product/db10g/bin/sqlplus / as sysdba <

[Dennis Handly · Accepted solution]

>[ ! -s sql.sql ] is this line to check if the file id empty??

Yes. It says NOT (!), size > 0 (-s) for that file.

[tom quach_1]

Thanks Dennis,

I test the whole script and it works well.

Thank you very much,
Tom

[tom quach_1]

Thank you