Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-27
14
Medium Priority
?
1,302 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 1000 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 1000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 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