Oracle Procedure – How to Write a Procedure to Kill Sessions Running Over 5 Minutes

oraclestored-procedures

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?

Best Answer

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.

Related Question