oracle 10g DBMS_SCHEDULER

 

Oracle 10g DBMS_SCHEDULER


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. 

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_position=>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' ;
 
 
 
 
 
To find out the job 

COL COMMENTS FORMAT A30 
COL REPEAT_INTERVAL FORMAT A40 
SET PAGESIZE 100 
SET LINESIZE 200 
SELECT job_name, repeat_interval,COMMENTS FROM dba_scheduler_jobs where job_name='RMAN_FULL_F'; 

To show running jobs:

select job_name 
,      session_id 
,      running_instance 
,      elapsed_time 
,      cpu_used 
from dba_scheduler_running_jobs;
 
 
 
 
to disable job 

BEGIN 
DBMS_SCHEDULER.disable (NAME => 'RMAN_FULL_F', FORCE=> TRUE); 
END;
/
 


to drop the job 

BEGIN 
DBMS_SCHEDULER.drop_job (job_name => 'RMAN_FULL_F'); 
END;