Hi Ravi,

If you are having this error:
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.CLK", line 12
ORA-06512: at line 1

Then try this:
create or replace procedure clk as
v_sid number;
v_serial number;
l_string varchar2(200);
v_bkgd number;
v_job number;
j_string varchar2(200);
v_dbname varchar2(10);

begin

select distinct b.job
into v_job
from v$session a,dba_jobs_running b,v$locked_object c,dba_jobs d
where a.sid=b.sid
and a.sid=c.session_id
and b.job=d.job
and upper(d.what) like '%SNAP_REF_WTDSLOTSGROUP%';
IF SQL%NOTFOUND THEN
RAISE no_data_found;
ELSE
dbms_output.put_line('Breaking the job.......');
j_string:='exec dbms_job.broken('||v_job||',true);';
execute immediate j_string;
END IF;

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No job found of type SNAP_REF_WTDSLOTSGROUP...
');
end;
/

set serveroutput on
exec clk



Note: U would be running this as SYS else direct grants are required....
Regis



*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************

Oracle LazyDBA home page