Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1574
  • Last Modified:

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

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
dba_shashi
Asked:
dba_shashi
  • 8
  • 6
3 Solutions
 
sdstuberCommented:
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
 
dba_shashiAuthor Commented:
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
 
sdstuberCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dba_shashiAuthor Commented:
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
 
sdstuberCommented:
I find profiles to be simpler.

I suggest try both and see which works better in practice for you
0
 
dba_shashiAuthor Commented:
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
 
sdstuberCommented:
yes CPU_PER_CALL is hundredths of a second to allow per call, which roughly equates to time per query or procedure
0
 
dba_shashiAuthor Commented:
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
 
sdstuberCommented:
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
 
dba_shashiAuthor Commented:
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
 
dba_shashiAuthor Commented:
As you even I am confused with application teams demand...

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

reporting services service.exe
0
 
dba_shashiAuthor Commented:
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
 
sdstuberCommented:
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
 
dba_shashiAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now