Search This Blog

Total Pageviews

Sunday, 18 December 2011

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;



No comments:

Oracle DBA

anuj blog Archive