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)||','
end loop;
end;
/
variable job number
begin
dbms_job.submit(:job, 'kill_session_proc;', sysdate, 'trunc(sysdate,''mi'')+1/7
end;
/
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)||','
end loop;
end;
/
variable job number
begin
dbms_job.submit(:job, 'kill_session_proc;', sysdate, 'trunc(sysdate,''mi'')+1/7
end;
/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
not every operation populates v$session_longops.
perhaps you want to use v$session with last_call_et
perhaps you want to use v$session with last_call_et
also note, once you kill a session, it can remain for a while, especially if it accumulated substantial rollback/undo that be applied.
ASKER
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/7
true,
:instno 1); (will this work if we schedule the job accross all 3 nodes one by one)?
end;
/
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/7
true,
:instno 1); (will this work if we schedule the job accross all 3 nodes one by one)?
end;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
Let me put this way..
How to create a job with the above sp to be called.
How to create a job with the above sp to be called.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great...
I am giving this query as below in my SP
select sid, serial#
from v$session
where program = 'REPORTINGSERVICESSERVICE.
username = 'USR';
oR
select
VS.sid, VS.serial#,opname,GVS.Prog
from v$session_longops VS,gv$session GVS
where
elapsed_seconds >200 and
time_remaining > 0 and
VS.username = 'USR'
and gvs.program='ReportingServ
But its not returning any values at all and I see the program or qurry running in the Session...
Thanks..
I am giving this query as below in my SP
select sid, serial#
from v$session
where program = 'REPORTINGSERVICESSERVICE.
username = 'USR';
oR
select
VS.sid, VS.serial#,opname,GVS.Prog
from v$session_longops VS,gv$session GVS
where
elapsed_seconds >200 and
time_remaining > 0 and
VS.username = 'USR'
and gvs.program='ReportingServ
But its not returning any values at all and I see the program or qurry running in the Session...
Thanks..
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?
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?
ASKER
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
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
>>> 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 .
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 .
ASKER
Looking at the solution provideed..it only served few areas. Finally chose Resource Manager tool from OEM and got mixed success.
ASKER
where elapsed_seconds > 300 and username = 'USR' and
time_remaining > 0 ;
Would it run this way...?
Thanks