2

How to write a procedure /job to kill all session running for more than 5mins in Oracle?

So, I query v$session and find out that for a particular user, program pair if the session has been active for more than 5mins kill it.

Also, how to write a job to run this procedure for that pair every minute until 5mins and then kill the process (by sid) ..

create or replace procedure kill_session( p_sid in number, p_serial# in number )
as
begin
    for x in ( select *
                 from v$session 
                where username = USER
                  and sid = p_sid
                  and serial# = p_serial# )
    loop
        execute immediate 'alter system kill session ''' || 
                 p_sid || ',' || p_serial# || '''';
        dbms_output.put_line( 'Alter session done' );
    end loop;
end;

How to check the time if the session has been active for more than 5mins and how to make it run till 5min for a particular user, program pair?

5
  • 1
    Which Oracle version and edition are you using? Also why are you killing the sessions? Commented Aug 2, 2014 at 0:11
  • 2
    WHERE status = 'ACTIVE' and last_call_et > 300 is what you are looking for. killing sessions like this is almost always a bad idea but you may have reasons. you also want to add TYPE = 'USER' so you don't end up killing background sessions. IF you kill certain background session, say bye bye to your instance. A better option might be to create a resource manager profile and assign it to users on a per session basis, specifically you need to assign cpu time quota. Hit up the manuals for all the good stuff.
    – Raj
    Commented Aug 2, 2014 at 3:36
  • there is also the possibility to use resource manager for this. But without version it's hard to give advice
    – Munchi
    Commented Aug 4, 2014 at 7:58
  • Oracle version 11.1.0.7 .. I`m trying to automate some parts of a system where user can remotely run queries from host A to any host and if the query runs for more than a certain time limit it should kill the session on the remote host as well as on host A to free up resources. Commented Aug 4, 2014 at 17:52
  • To create job for killing session, please visit below link mujahidalishahani.blogspot.ae/2014/11/…
    – user52973
    Commented Nov 26, 2014 at 14:53

1 Answer 1

2

Generally, aborting database queries and DML is not a good idea, and as @Raj already pointed out, killing sessions is also bad idea – actually it's even worse.

Your sessions may be running some transactions which consume undo space (in the undo tablespace of the user), hold locks on tables and occupy space (e.g. for row sorting) in the temporary tablespace. When you kill the session, all the work done by the transaction in-flight should be rolled back, all the undo segments and locks should be released. And while "light" sessions can terminate pretty quickly, others can take time before Oracle engine cleans everything up.

To answer your question we should know why you are killing your sessions.

  • Is it because the user sessions consume a lot of resources (CPU) if you allow them more than 5 minutes to run?
  • Is it because the users establish too much of sessions and idling?
  • Is it because the newly established session starves for resources already used by other sessions and should have them available to run?
  • Is it because you developed some cool software and you want to limit users to evaluate it only a few minutes before they are disconnected?

Probably you could improve your business logic so as to avoid killing sessions – doing computations on the server side, for example.

Or you could develop some RESTful web application which is run in the middle tier (application server) and performs queries on behalf of users while keeping a reasonable small number of connections to the database using connection pool. For example, you could write Java web application with servlets and JSP using Apache Tomcat JDBC Connection Pool (official documentation for Tomcat 8). You could also switch from dedicated server architecture in favor of shared server architecture.

If your software doesn't support connection pooling you could employ Database Resident Connection Pooling via OCI.

But if nothing I suggested to you meet your needs, you can implement your resource plan using Database Resource Manager, define directives which abort running queries or kill sessions which run or idle too long, and everything you need to implement this is described in chapter Managing Resources with Oracle Database Resource Manager in Oracle Database Administrator's Guide. The keywords are CANCEL_SQL, KILL_SESSION, MAX_EST_EXEC_TIME, MAX_IDLE_TIME.

2
  • Ya I have a python web-app through which users can run queries and I limit the no. of connections to the db, but the only problem is cases where user doesn`t know how good his query plan is and runs a cartesian join kinda query which takes a long time to run as the datasets are large. So, you suggest I use the RESOURCE MANAGER to resolve it as once the query has been issued there is nothing much the webapp can do than to kill its local session and the remote session somehow on the database... right? Commented Aug 5, 2014 at 17:45
  • If you will be using Database Resource Manager (DRM), assigning users to consumer groups, defining resource plan directives associated with the consumer groups and the resource plans you will have implemented, you will be able to kill user sessions and cancel SQL, and your app will not have to kill the sessions – all the job is done by DRM based on directives which have CANCEL_SQL and KILL_SESSION set in them. If you need an example, I can make one for you, though, as I said, all you need to know to implement your own DRM plan is available in Administrator's Guide, along w/samples. Commented Aug 8, 2014 at 3:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.