Solved

How to kill active session automatically that runs for more than 5 min in oracle

Posted on 2011-09-27
14
1,213 Views
Last Modified: 2012-05-12
Hi Experts

Application team runs an appilication (Report Builder), it will run a query on database,  I need to schedule a kill session if the query runs more than 5 minutes so that query will be killed automatically after 5 minutes.

Thanks and kind regards
Shashi singh
0
Comment
Question by:dba_shashi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 36710502
alter the user's profile


ALTER PROFILE  your_user_profile LIMIT  CPU_PER_CALL 30000   --- it's measured in 1/100ths of a second
0
 

Author Comment

by:dba_shashi
ID: 36711061
Can't we do with resouse limit ? Is there other way out? As said its one user which will run many quey but needs to close once it runs for more than 5 min.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 36711096
yes, you could probably use a resource plan, something like this...


 SYS.DBMS_RESOURCE_MANAGER.update_plan_directive(
     plan => 'YOUR_RESOURCE_PLAN',
     group_or_subplan => 'YOUR_GROUP',
     new_max_est_exec_time => 300);

using profile, you would simply assign that profile to the one user.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:dba_shashi
ID: 36711368
Thanks Sdstuber for prompt and guided solution.

Finally which would be better option considering the scenario given ? Has to be simple and stright forward.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36711876
I find profiles to be simpler.

I suggest try both and see which works better in practice for you
0
 

Author Comment

by:dba_shashi
ID: 36712085
This is only for one User alone.
The user is attached to a profile...As you said profile would be better.

CREATE PROFILE APP_PROF LIMIT
  SESSIONS_PER_USER DEFAULT
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL -------(30000) this is what we are talking about  for 5 min limit) is it correct ?
  CONNECT_TIME DEFAULT
  IDLE_TIME DEFAULT
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS DEFAULT
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME DEFAULT
  PASSWORD_REUSE_MAX DEFAULT
  PASSWORD_LOCK_TIME DEFAULT
  PASSWORD_GRACE_TIME 5
  PASSWORD_VERIFY_FUNCTION DEFAULT;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36712128
yes CPU_PER_CALL is hundredths of a second to allow per call, which roughly equates to time per query or procedure
0
 

Author Comment

by:dba_shashi
ID: 36712480
Hi sdstuber  :)
another doubt...

can we do the same thing...for say if the qurry returns 2000 rows and it should terminate automatically after 2000 rows return, given the same scenario....?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36712494
no, neither profiles nor resources will do row counts.

you could alter your query to add a ROWNUM condition...


select * from ( your query)
where rownum <= 2000
0
 

Author Comment

by:dba_shashi
ID: 36712706
You mean to say it has to be triggered from Application user side, who is running the querry? As a dba can we set something like above...

One more thing..
I did change in profile for
CPU_PER_CALL 3000 to see how it behaves...but the qurry ran for more than 1 min and gave the result..
Did I missed something here?
0
 

Author Comment

by:dba_shashi
ID: 36712867
As you even I am confused with application teams demand...

what about killing this services itself...automatically...

reporting services service.exe
0
 

Author Comment

by:dba_shashi
ID: 36718294
Hi sdstuber

AS suggested I did the changes in the profile of the user but it did not work.
I guess, the CPU time never went beyond 30 sec for the operation..that's reason it did not work..chances are there were other resources which were busy doing the work, such as I/O etc.

What do you suggest..
Thanks
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 36718309
you could try resource manager

you could create a dbms_job/dbms_scheduler process that looks for your process (checking user, v$sql, v$session for client info etc, whatever is appropriate)
and this issues alter system to kill the process
0
 

Author Comment

by:dba_shashi
ID: 36815787
Thanks sdstuber..
Taking a hint from you..I did this..

Alter PROFILE NAPPBAT_PROF LIMIT
    CONNECT_TIME 5;

ALTER SYSTEM SET resource_limit=TRUE;

And it worked.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question