You Asked

Dear TOM,
Hi

Is there anyway to kill the users who are idle for more than six hrs.

Regards
Gopesh

and Tom said...

You can use profiles (see concepts and admin guide) or you can use dbms_jobs to look for and kill sessions on a recurring basis. Profiles have the downside that they will send a kill to the client and wait for the client to acknowledge -- so the sessions will tend to stay in the database until the client does something in the database -- discovers it was killed and then goes away.

To use dbms_jobs and plsql for example, if you use the last_call_et column in v$session for INACTIVE sessions, you can find all users inactive for 6 hours of more:

1 select 'alter system kill session ''' || sid || ',' || serial# || '''; /* ' || username || ' */'
2 from v$session where username is not null
3 and status = 'INACTIVE'
4* and last_call_et/60/60 >= 6
ops$tkyte@8i> /

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';/*'||USERNAME||'*/'
----------------------------------------------------------------------------------------------------
alter system kill session '7,11639'; /* REVIEW */
alter system kill session '59,5939'; /* ATCDEMO */

If you use dbms_sql to dynamically execute SQL (make sure to grant ALTER SYSTEM to the owner of the procedure, see </code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>for the reason why), you could write a procedure that is run every N minutes using dbms_jobs to kill these idle sessions.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Create dynamic "profile" for idle time

Sten Jones, October 27, 2004 - 1:00 pm UTC

I want users to have unlimited idel time during working hours but outside rather restricted. Profiles will not do that but perhaps dbms_jobs. Thanks

October 27, 2004 - 1:27 pm UTC

you'd have to schedule a job to do that -- and I'm not sure that the alter of the profile would affect existing sessions, that you would have to test.

profile downside

Rob, October 27, 2004 - 2:40 pm UTC

<quote>Profiles have the downside
that they will send a kill to the client and wait for the client to acknowledge -- so the sessions will tend to stay in the database until the client does
something in the database<quote>

Could you clarify this please. I was thinking once I set the idel time in a profile and attach it to the user, that should take care of killing the session if it exceeds certain idle time? From your comments, it looks like it does not do it this way. Thanks.

October 27, 2004 - 4:26 pm UTC

it kills it -- session would be marked as killed, end user it effectively logged out, they cannot use that session anymore.

but the killed session stays there so we can notify them when they do something that "hey, you were killed, goodbye"

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:209412348074

Daniel Wattier, October 07, 2005 - 10:52 am UTC

Great

Thank you !!

what is diff. then between explicit kill and profile

lyxx, October 05, 2007 - 8:44 am UTC

Hi Tom,

I am trying to find the diff. between killing the sessions explicitly by issuing "alter system ..' and get them killed
by setting the profile.

One session which was SNIPED because of profile setting released all the locks as well.

So, if session leaves in v$session then what is diff.
in killing them through script/job as still they would be
in v$session in this case also in KILLED state.

Regards.

Breadcrumb

Question and Answer