123

begin

for r in (

 select * from v$session

   where status = 'ACTIVE'

     and audsid <> userenv('SESSIONID')

     and sid in

     ( SELECT sid FROM dba_jobs j join dba_jobs_running r using(job) where what like '%T_pkgOperDay.pExec4job%'

     UNION ALL

     SELECT session_id FROM dba_scheduler_running_jobs where job_subname is not null)

) loop

 begin

 execute immediate 'ALTER SYSTEM KILL SESSION '''||r.sid||','||r.serial#||''' IMMEDIATE';

 dbms_output.put_line( 'Killing '||r.sid||','||r.serial#||' was success');

 exception when others then

  dbms_output.put_line( 'Error killing '||r.sid||','||r.serial#||chr(10)||SQLERRM);

 end;

end loop;

end;

/

begin

 for r in (SELECT code FROM c_job where code like 'JOB_021%') loop

update /*+rule*/c_vcs

set   usr_id = (SELECT id FROM c_scm_usr where terminal = sys_context('USERENV','TERMINAL') and os_user = upper(sys_context('USERENV','OS_USER'))) ,

      modfl = 0,

      lockfl = 0,

      dlock = sysdate

where code =r.code;

commit;

end loop;

end;

/

begin

  for r in (SELECT * FROM c_job where run_proc like '%T_pkgOperDay.pExec4job%') loop

   C_PKGJOB.KILLJOB(IID => r.id);

   C_PKGJOB.CREATEJOB(IID => r.id);

 end loop;

end;

/