Oracle DBMS_SCHEDULER example

The DBMS_JOB package is replaced by the DBMS_SCHEDULER package in oracle 10g
( oracle job )


login as sys or system

sqlplus / as sysdba

sqlplus>


this job will run daily 4 a.m


begin
dbms_scheduler.create_job
(
job_name => 'RMAN_FULL_F',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 ,
number_of_arguments => 1,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0',
enabled => false,
comments => 'oracle backup RMAN '
);
end;
/

PL/SQL procedure successfully completed.

(start_date =>sysdate+ 1/288 this will start job after 5min )

Notice that the JOB_TYPE can be PLSQL_BLOCK, STORED_PROCEDURE, and EXECUTABLE.
The REPEAT_INTERVAL
can be HOURLY, DAILY, MINUTELY, YEARLY or BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, BYSECOUND.
For example, FREQ=BYWEEKNO=4,7,52 or BYDAY=MON, etc.




exec dbms_scheduler.set_job_argument_value(job_name=>'RMAN_FULL_F',argument_pos
ition=>1 ,argument_value=>'/aptus/oracle/admin/aptdb/script/rman_oracle.sh') ;

PL/SQL procedure successfully completed.

(rman_oracle.sh is the novagenesis Rman backup file )

to eable the job

SQL> exec dbms_scheduler.enable(name=>'RMAN_FULL_F');

PL/SQL procedure successfully completed.



to see the detail of job
select status,run_duration,actual_start_date,additional_info from dba_scheduler_job_run_details
where job_name='RMAN_FULL_F'