Kill The Inactive Session After 2 Hours

[harrygulls]

Can some body help in wrting the script for this to
KILL THE INACTIVE SESSION AFTER 2 HOURS ( if it is has inactive from last 2 hours or more.)

Thanks In Advance

[Sameer]

Why don't you use profile (IDLE_TIME) ?

[harrygulls]

Thanks.
Can you give me an example.

[adewri]

Try this

SET RESOURCE_LIMIT=TRUE in your init.ora file

bounce ur DB

then

CREATE PROFILE IDLETEST LIMIT IDLE_TIME 120;

alter user TESTUSER profile IDLETEST;

HTH
Amar

[Sameer]

As adewri wrote, you need to create profile and assign it to the user.

CREATE PROFILE IDLETEST LIMIT IDLE_TIME n; where n is time specified in minutes.

IDLE_TIME: Specify the permitted periods of continuous inactive time during a session.

[jmodic]

However setting profile with the IDLE_TIME will not actually kill the session after the specified time of inactivity! That session will stay there in inactive state even after the specified idle time. It will only be disconnected when next activity on that session is attempted. So if user doesn't get back and try to do something after two hours of inactivity, the session will still be hanging there on the database. It will still eating the resources, prossibly forewer if you don't kill it manually!

[alapps]

Use the v$session view to find the current sessions.

The LOGON_TIME field will give the logon time of a session.

If SYSDATE - LOGON_TIME is greater than 2 hours, then you found a session that has been active longer than two(2) hours.

Kill the session manually as jmodic stated
ALTER SYSTEM KILL SESSION 'sid,serial#'

P.S. Make sure you do NOT kill the oracle internal processes. If you do, you will crash the DB.

[pando]

you cannot use SYSDATE - LOGON_TIME to determine if a session can be killed or not

[jmodic]

Of course, LOGON_TIME doesn't tell you anything about how long the sesson has been inactive. For that you have to check column LAST_CALL_ET, which stores the elapsed time in seconds from the last call to the RDBMS by that session.

To find all the sessions that have been inactive for more than 2 hours, your query would look something like:

SELECT * FROM v$session
WHERE STATUS != 'ACTIVE'
AND last_call_et > 2*60*60;