1. Stop those jobs
SQL> Execute dbms_job.remove(xx); -- if you used dbms_job to submit them
SQL>
begin
dbms_scheduler.drop_job(
job_name=>'JOB_NAME',
force=>true);
end;
/
begin
dbms_scheduler.drop_schedule(
schedule_name=>'SCHEDULE_NAME',
force=>true);
end;
/
begin
dbms_scheduler.drop_program(
program_name=>'PROGRAM_NAME',
force=>true);
end;
/
-- If you used dbms_scheduler
The views:
dbms_job
dbms_scheduler
dba_scheduler_job_run_details
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_RUNNING_JOBS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOBS
2. Using content of what column, program in dbms_scheduler to detect what's sql/proc/func caused db hang. Trace them by explain table, tkprof..