Solved

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

Posted on 2011-09-27
14
1,113 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 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
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: 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

821 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