May 23, 2008

Oracle 10g Job scripts

Oracle 10g Job scripts:

1. To enable or fix broken jobs in Oracle 10g

BEGIN
DBMS_SCHEDULER.ENABLE ('ORACLE_JOB');
END;
/
2. Modifying or setting Oracle 10g Jobs
begin
dbms_scheduler.set_attribute (
name => 'ORACLE_JOB',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=22;BYMINUTE=0'
);
end;
commit;
3. See Oracle job details
i) Job information:
select *from dba_scheduler_jobs
WHERE job_name in ('ORACLE_JOB');
ii) Job run details:
select log_date,to_char(log_date,'DAY'),
additional_info
from dba_scheduler_job_run_details
WHERE job_name ='ORACLE_JOB'
order by log_date desc;
iii) Running job details:
select *
from dba_scheduler_running_jobs
WHERE job_name in ('ORACLE_JOB');
iv) Job logs:
select * from dba_scheduler_job_log
WHERE job_name in ('ORACLE_JOB')
order by log_date desc;
v) Job session details:
select jr.job, s.username, s.sid, s.lockwait, s.logon_time
from dba_jobs_running jr, v$session s
where jr.sid = s.sidorder by jr.job;

No comments: