SCHEDULER_ADMIN role for job scheduling
CREATE JOB privilege for creating jobs
-- Schedule job
begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin package.procedure(''param_value''); end; '
,start_date => '01/01/2006 02:00 AM'
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;
/
-- Monitoring job-scheduling
-- to show all jobs and their attributes:
> select
owner
,job_name
,start_date
,end_date
,enabled
from dba_scheduler_jobs
where
-- enabled='TRUE' and
owner <> 'SYS';
enable/diable/start/stop job
> execute dbms_scheduler.disable(job_name);
> execute dbms_scheduler.enable(job_name);
> exec dbms_scheduler.drop_job(job_name);
> exec dbms_scheduler.disable(job_name);
> exec dbms_scheduler.enable(job_name );
> exec dbms_scheduler.stop_job(job_name);
-- To show running jobs:
> select
job_name
,session_id
,running_instance
,elapsed_time
,cpu_used
from dba_scheduler_running_jobs;
-- To show details on job run:
select log_date
, job_name
, status, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details;
-- To show job history:
select log_date
, job_name
, status
from dba_scheduler_job_log;
-- show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;
-- show all program-objects and their attributes
select *
from dba_scheduler_programs;
-- show all program-arguments:
select *
from dba_scheduler_program_args;
Hope this Help! Rupam
