[Oracle] Automatically Kill inactive Sessions

Posted by cikul | Posted in Oracle, Programming, SQL, Technology | Posted on 28-09-2012-05-2008


The problem you could face with arbitrary killing of inactive sessions is that you may kill a session that was still running (it could have just completed one procedure & hadn’t yet started another).

Perhaps an easier way to accomplish what you want (although I’m guessing here at what it is you want to do!) is to set the users’ profiles to automatically kill their session after a specified period of time e.g.

create profile XXX
 idle_time 30;

will create a profile called XXX that will disconnect users that are inactive for 30 minutes – you can then assign this profile to your users by:

alter user [username]
 profile XXX;

By default, all users use the oracle-supplied DEFAULT profile, so you can, if you wish,

SQL> select username,profile from dba_users;

USERNAME                       PROFILE
------------------------------ ------------------------------
SYS                            DEFAULT
SYSTEM                         DEFAULT
DBSNMP                         DEFAULT
IVR                            DEFAULT
ELEARNING                      DEFAULT
SQL> select * from DBA_Profiles

Bear in mind that any changes to profiles will not take effect until you alter the resource_limit parameter to ‘true’ :

SQL> alter system set resource_limit=TRUE;

System altered.

SQL> show parameter resource limit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources                    integer     968
resource_limit                       boolean     TRUE
resource_manager_plan                string

Also, you need to consider what other parameters (if any) you may wish to set up or change within the profile.


Share and Enjoy

Write a comment