Create Oracle Jobs using DBMS_SCHEDULER Package
Privileges required for creating or managing job schedulers:
a. create job (for regular user)
b. manage scheduler (for database administrator)
sqlplus / as sysdba
grant create job, manage scheduler to TESTUSER;
alter session set current_schema=TESTUSER;
begin
dbms_scheduler.create_job(job_name => 'TESTUSER.REFRESH_USER_QUOTAS',
job_type => 'STORED_PROCEDURE',
job_action => 'SP_REFRESH_USER_QUOTAS',
start_date => to_date('01-06-2014 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Monthly;ByHour=00;ByMinute=00;BySecond=00',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => 'REFRESH USER QUOTAS');
end;
/
Change Job Priorities:
exec dbms_scheduler.set_attribute(name => 'REFRESH_USER_QUOTAS', attribute => 'job_priority', value => 1);
Views:
-- Views for checking states of scheduled jobs
user_scheduler_jobs
all_scheduler_jobs
dba_scheduler_jobs
-- Views for cheching the progress of current running jobs
user_scheduler_running_jobs
all_scheduler_running_jobs
dba_scheduler_running_jobs
