Session control between Oracle and Websphere 2

[uparna]

Hi ,
I am using websphere as the webserver with Oracle 8.1.7.1 as the backend .We are already using connection pooling and all our jsps have con.close (closing of unused connections) being used in the right places. The problem is this , somehow the session killing is not working properly at all . I mean , we have a minimum of 300 users connected at any point of time , and after a day , i still see nearly 200+ inactive sessions in oracle.
(
i use the following query :
select status,count(*),to_char(sysdate,'dd/mm/yyyy_hh/ss') "DATE_TIME"
from v$session
group by status;
)
I have not designed any profile for the user (to which the webserver connects to) yet . I fear that if i set the profile to a 15 minute inactive timeout or something , i may get "socket write failed" exception (we encountered this problem before with JavaWebServer) . But still , that is not the point here. I wonder why the sessions are not being killed. This is a problem because the inactive sessions bring down the performance of my DB server drastically . i wonder why . Moreover , i have forgotten what the sql query is for killing sessions given a session id . I know this is possible , but have forgotten the query.
Foof ! looks like too many questions in one post !
Request one and all to respond at the earliest incase you can help me !

Help!


Thanks in advance,
Regards,

Jayaram.

 

[stevecal]

Jayaram, this might help you...

Determine active/inactive sessions (and kill them…)

SELECT sid,serial#,status,server FROM v$session WHERE UPPER(username) LIKE '%';

When you know the sid and serial# then you can kill them if you have sufficient privileges by passing the values of sid and serial#:

ALTER SYSTEM KILL SESSION (sid),(serial#);

 

[stevecal]

NB: Oracle doesn't always tidy up open processes so you might want to run a Unix command like

kill -9 <pid-id>.

PS: You can get pid-id as follows:

select vs.sid, vs.serial#, vs.status, vp.spid
from v$process vp, v$session vs
WHERE UPPER(vs.username) LIKE '%';

 

[uparna]

Hi Steve,
I gave the commands that you directed me to and got this result as the first 2 rows :



SID SERIAL# STATUS SERVER
------- ---------- -------- ---------
7 2406 INACTIVE DEDICATED
8 920 INACTIVE DEDICATED



but then , when i try to give the kill command , i get the following error :

ALTER SYSTEM KILL SESSION (7),(2406)
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
ALTER SYSTEM KILL SESSION ('7'),('2406')
*
ERROR at line 1:
ORA-00026: missing or invalid session ID


what is the problem here ? Would be glad if you could guide me in this aspect.

Thanks in advance.
Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.

 

[uparna]

Hi ,
I found the solution myself. The correct syntax is :

ALTER SYSTEM KILL SESSION '33,94' IMMEDIATE;


Thanks again for the help. Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.