• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 852
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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