• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

DBMS_JOB to kill session over 2 min through a procedure

Hi Experts,

Please review the Proc and Job, and let me know if its fine. Its throwing an error in 10 or 20 seconds.
please advice..
Thanks very much

create or replace procedure kill_session_proc as
cursor c1 is
select sid, serial#
from v$session_longops
where elapsed_seconds > 300 and
username = 'USR';
begin
for c1_rec in c1 loop
execute immediate 'alter system kill session '||''''
||to_char(c1_rec.sid)||','||to_char(c1_rec.serial#)||'''' ;
end loop;
end;
/
variable job number

begin
dbms_job.submit(:job, 'kill_session_proc;', sysdate, 'trunc(sysdate,''mi'')+1/720');
end;
/
0
dba_shashi
Asked:
dba_shashi
  • 8
  • 7
3 Solutions
 
dba_shashiAuthor Commented:
Can I add this to the procedure..

where elapsed_seconds > 300 and username = 'USR' and
time_remaining > 0 ;

Would it run this way...?

Thanks
0
 
sdstuberCommented:
yes, you should be able to add the time_remaining clause.

you say it's throwing an error.  What is the error?
0
 
dba_shashiAuthor Commented:
Its not showing the any rows where username is 'USR'.
But the 'USR' session(query) is still running.
Last time you said ...

We can user resource manager.  I dont know how to do that.

We want the query to be killed which runs for more than 5 min for a specific user in his session.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
not every operation populates v$session_longops.

perhaps you want to use v$session with last_call_et
0
 
sdstuberCommented:
also note, once you kill a session,  it can remain for a while, especially if it accumulated substantial rollback/undo that be applied.
0
 
dba_shashiAuthor Commented:
Thanks sdstuber for input..

Since this is for 3 instance node...
Is there any way we ..
variable job number

begin
dbms_job.submit(:job, 'kill_session_proc;', sysdate, 'trunc(sysdate,''mi'')+1/720')
true,
:instno 1); (will this work if we schedule the job accross all 3 nodes one by one)?
end;
/
0
 
sdstuberCommented:
your syntax wasn't quite right, but close

assuming :instno is populated before calling this,it should work.
remember to commit when done,  dbms_jobs are transaction controlled, so they won't take affect until commit

BEGIN
    DBMS_JOB.submit(:job,
                    'kill_session_proc;',
                    SYSDATE,
                    'trunc(sysdate,''mi'')+1/720',
                    TRUE,
                    :instno                    
                   );
END;
0
 
dba_shashiAuthor Commented:
Thanks..

Do I need to run under my schema? or do I need to run this under concerned user schema?
I am stil getting the query runining for more than 20 min...

Something wrong with SP ? Do the SP takes joins on v$views?
0
 
dba_shashiAuthor Commented:
Let me put this way..

How to create a job with the above sp to be called.
0
 
sdstuberCommented:
the procedure should not use v$session_longops view  - as noted above,  this view won't be populated for all sessions
  use v$session - every session is in this view.

the dbms_submit I posted avoe created the job to call the procedure.

as noted above, you must  commit; in order for your submit to work.

the procedure must be owned by a user with privileges to select on those views, and must have alter system privilege. - these privileges must be granted to the user directly,  NOT through a role

you can submit the job as anybody that can execute the procedure
0
 
dba_shashiAuthor Commented:
Great...
I am giving this query as below in my SP

select sid, serial#
from v$session
where program = 'REPORTINGSERVICESSERVICE.EXE' and
username = 'USR';

oR

select
VS.sid, VS.serial#,opname,GVS.Program,VS.username
from v$session_longops VS,gv$session GVS
where
elapsed_seconds >200 and
time_remaining > 0 and
VS.username = 'USR'
and gvs.program='ReportingServicesService.exe'

But its not returning any values at all and I see the program or qurry running in the Session...

Thanks..
0
 
sdstuberCommented:
the second one is wrong as I've noted above.

if the first one doesn't work, why doesn't it?  What are you seeing in v$session that the first query can't find?
0
 
dba_shashiAuthor Commented:
Elapsed_seconds
and Time_remaining...  These are two fields that v$session do not have.
As said earlier..sp does not take any join in their where clause. Please correct me if I am wrong.
thanks

0
 
sdstuberCommented:
>>> Elapsed_seconds

http:#36933088   use last_call_et


you won't be able to derive time_remaining,  you could try doing an outer join to logops , and "if" your session has long ops populated you could try using it, but as noted above, most operations don't populate longups values .
0
 
dba_shashiAuthor Commented:
Looking at the solution provideed..it only served few areas. Finally chose Resource Manager tool from OEM and got mixed success.
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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