• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1649
  • Last Modified:

SQL statment for Killing Sessions

Hi!

I need a sql statement which will kill all inactive sessions.
Is it possible to schedule running of this statement every day at 1:00 AM?

TNX
0
Premanidhi
Asked:
Premanidhi
1 Solution
 
baonguyen1Commented:
You can try this:

SQL>alter system kill session 'sid,serial#'

where the sid and serial# are from v$session view

Please note that as soon as you run a query and get the results back
in sqlplus, you will see  "inactive" seesions andmost sessions are mostly inactive.

A good way is to set an IDLE time in the PROFILES .  That will kill sessions that have been IDLE for "x" minutes.  Just killing inactive sessions would *not* be a good idea.

Hope this helps

0
 
PremanidhiAuthor Commented:
baonguyen1 Could You please explain me how to set IDLE time in the PROFILES?
 
0
 
baonguyen1Commented:
You can do:

1. create a profile:

SQL>CREATE PROFILE <profile_name> LIMIT
IDLE_TIME <time in minute>;

For example:

SQL>CREATE PROFILE resourec_limit LIMIT
IDLE_TIME 60;

IDLE_TIME is the number of minutes a session can be connected to the db without being actively used.

Now grant profile to a user:

SQL>ALTER USER <USER>  PROFILE <PROFILE_NAME>;

For example:

SQL>ALTER USER ABC  PROFILE resourec_limit;

Hope this helps
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
boriskalavskyCommented:
The process will be sniped on IDLE time and then cleaned up by PMON (some times it does not work).
0
 
idaiCommented:


but AFAIK you have to set init.ora parameter RESOURCE_LIMIT=True
0
 
vanmeerendonkCommented:
You could also use the last_call_et from v$session
sysdate - last_call_et/86400 is the last datatime the session "did"something.
We kill everybody that has done nothing for a day
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now