Shell Script problem

[GerGon]

Hi gurus..

I want to know who access Oracle database by sqlplus, I want to do it, get the user out from listener.log file and then kill his session.

++++I have one file listener.log with similar format like:
asfd(sid=xx)xdxs(prg=sql)(user=yy)(wii3id)ase2
asfd(sid=xx)xs(prg=forms)(user=y1)(wii3id)ased
asfd(sid=xx)xdxs(prg=RWD)(user=y2)(wii3id)as82
asfd(sid=xx)xdxs(prg=sql)(user=y3)(wii3id)asv2
+++ I execute cat listener.log|grep -i sql > f1
asfd(sid=xx)xdxs(prg=sql)(user=yy)(wii3id)ase2
asfd(sid=xx)xdxs(prg=sql)(user=y3)(wii3id)asv2
+++ I want to extract wich user used sqlplus,
+++ I'm using strstr but it fails,msg:
sel=`cat f1`
for i in $sel
do
var1=`strstr($i,"USER=")`
echo $var1 >> file1
done
./killsql[7]: syntax error at line 1 : `(' unexpected
+++ What's wrong???

*** I want to extract: the user who access by sqlplus program and kill his session.

Any other idea be welcome..

Thanks

[Geoff Wild]

Are you using ksh or sh?

Rgds...Geoff

[Rajeev Tyagi]

Gergon,

It can be done with awk

cat f1 | awk -F"user" '{print $2}' | awk -F"=" '{print $2}' >file1


Rajeev

[GerGon]

Thanks Geoff, I'm using ksh...

I want to know How to extract son substrings inside string... Maybe sed...

[Peter Godron]

Hi,
my listener.log file doesn't look like your example. So how about:
ps -ef | grep -e ' sqlplus ' | grep -v grep | cut -d' ' -f2
This should give you the processes of all the sqlplus sessions.

[c_51]

can not tell if your listner.log file is using as a seperator, but you could do somehting like this:

oldIFS="$IFS"
IFS="()\n"
cat listener.log |
while read w1 w2 w3 w4 w5 w6 rest
do
print $w1 $w2 $w3 $w4 $w5 $w6
prg=${w4:#*=}
user=${w6:#*=}
print $prg $user
if [[ "$prg" = "sql" ]] ;then
print "die $user"
fi
done
IFS="$IFS"

[Jannik]

This is not the best script but it works :-)
#!/usr/bin/ksh

for i in $(cat f1)
do
echo $i | grep user | awk 'FS="=" {print $4}' | awk ' FS=")" {print $1}' >> file1
done

[GerGon]

I try with all your efforts, but they did not work...

When I do the first filter with grep the out file only have one large string...

It's better, How do I do to get a substring from string, ie:

a1='geosphere(user=delta)green(prg=alfa)blue'

Grep see it like one string (no separated), I need to get or extract "delta" and "alfa", BUT remenber the fix parameter is user and prg, because delta and alfa are variables.

Then, I process the file properly...

Can help me please...
Thanks..

[GerGon]

Peter, I know that the listener.log have a different format.

But, if I know How to get a substring from string a can do it with listener.log

However, if someone have Oracle and want to review the original file listener.log of your Oracle Environment and give me some advice, thanks..

Thanks

[Geoff Wild]

Can't seem to get the strstr to work...

How about this ugly hack:

cat f1 |awk -Fuser= '{print $2}' |awk -F")" '{print $1}'

In a script:

#/bin/sh
for user in `cat f1 |awk -Fuser= '{print $2}' |awk -F")" '{print $1}'`
do
echo $user
done

Rgds...Geoff

[Bharat Katkar]

Hi Gergon,
I am successful with you single line question asked later:

#cat file1
a1='geosphere(user=delta)green(prg=alfa)blue'

Now in two shots i get the required output:

#cat file1 | cut -d "(" -f 2 | cut -d ")" -f 1

user=delta

# cat file1 | cut -d ")" -f 2 | cut -d "(" -f 2

prg=alfa

Looks funny :))
but still does that help ??

Regards,

[Jean-Luc Oudart]

Well if the user does not user SQLNet you won't find him/her in the listener.log

e.g.
export ORACLE_SID=
sqlplus user/passwd

this is not in the listener.log

you must go into the database qnd query the system views :
try :
select * from v$session where module='SQL*Plus'

then find SID,serial# and kill session at oracle level.

Regards
Jean-Luc

[Fred Ruffet]

Maybe it's time to go for perl. What you want to do is exactly what perl was designed for.

Here is a try :

------ connection.pl ---
#!/opt/perl/bin/perl

while () {
chomp;
@equals=split /[()]/;
$host="";
$user="";
$service="";
foreach $equality (@equals) {
($key,$value)=split /=/,$equality;
if ($key eq "HOST") { $host=$value; }
elsif ($key eq "USER") { $user=$value; }
elsif ($key eq "SERVICE_NAME") { $service=$value; }
}
print "Connection from ${user}\@${host} to ${service}\n";
}

------ usage ---

tail listener.log | ./connections.pl
Connection from scott@127.0.0.1 to oradb1
Connection from system@192.17.38.1 to prod
Connection from fred@192.17.1.1 to oradb1
...

----- end ---

Regards,

Fred

[GerGon]

Fred,

Your solution looks good and works fine, BUT, How can i filter only user that connect via: sql* that mean:
sqlplusw, sqlplus32 and SQLNav?????

Excuseme but don't know Perl, otherwisse, I only do the correction..

Thanks..

[Steve Faidley]

Am I missing something? Are you just trying to get the sql user for output?

# egrep "prg=sql" f1 | cut -d= -f4|cut -d\) -f1
yy
y3
#

Add a for loop around it

# for USER in `egrep "prg=sql" f1 | cut -d= -f4|cut -d\) -f1`
do
sqlkill $USER
done

## I just did that on my SUN workstation. If that doesn't work on your HP use grep -e"prg=sql" instead of egrep "prg=sql"

[Fred Ruffet · Accepted solution]

This version seems to match your requirements. I have added a criteria on SID as well as SERVICE_NAME because there are still old tnsnames.ora files :)

This will catches any line were program name mathes sql Sql sQl sqL SQl, etc. Note that regarding my tests, there are lots of programs named *sql*...

if you want to have only those starting with sql, you will need to add a ^ at the begining of regexp.

------ connection.pl ---
#!/opt/perl/bin/perl

while () {
chomp;
@equals=split /[()]/;
$host="";
$user="";
$service="";
$program="";
foreach $equality (@equals) {
($key,$value)=split /=/,$equality;
if ($key eq "HOST") { $host=$value; }
elsif ($key eq "USER") { $user=$value; }
elsif ($key eq "SERVICE_NAME" or $key eq "SID") { $service=$value; }
elsif ($key eq "PROGRAM") { $program=$value; }
}
$program=~ m/[Ss][Qq][Ll]/g;
print "Connection from ${user}\@${host} to ${service} with ${program}\n" if pos($program);
}
------ end ---

Regards,

Fred

[Gordon Morrison_1]

for line in `cat listener.log|grep prg=sql`
do
temp=${line##*prg=sql\)\(user=}
sqluser=${temp%%\)\(*}
echo "user $sqluser used sql"
done

The above will find the user that used sql.
Am I right in assuming that the "(sid=xx)" part of the line would actually contain the PID of their session? i.e. (sid=4509)?

If so, try this:

for line in `cat listener.log|grep prg=sql`
do
temp=${line##*prg=sql\)\(user=}
sqluser=${temp%%\)\(*}
echo "user $sqluser used sql... Killing him."
temp=${line##*sid=}
victim=${temp%%\)*}
echo "victim is ${victim}"
kill $victim
done

[GerGon]

Thanks a lot Everybody in this thread....