Solved

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

Posted on 2011-09-27
14
989 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
  • 8
  • 6
14 Comments
 
LVL 73

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 73

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
 

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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now