October 5, 2011 at 6:14 am
Hi folks,
I am a core Sql server developer, but now,i need to work on oracle DB. here i want to execute a procedure automatically,(same as job procedure in sql server, create a procedure and schedule it in maintenance plan).
how can i achieve it in oracle.
any help will be highly appreciated.
better to give me some examples also.
October 6, 2011 at 5:49 am
ghanshyam.kundu (10/5/2011)
I am a core Sql server developer, but now,i need to work on oracle DB. here i want to execute a procedure automatically,(same as job procedure in sql server, create a procedure and schedule it in maintenance plan).how can i achieve it in oracle.
This can be either done via DBMS_SCHEDULER or by writting a shell script and scheduling it via crontab.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 6, 2011 at 11:11 am
thanks paul,
i will check it.
October 24, 2011 at 7:49 am
If you are looking for the gui interface, OEM (Oracle Enterprise Manager) should be quite easy to understand.
November 16, 2011 at 12:37 pm
in PL/SQL:
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'DBSCHEMA.JOB_NAME'
,start_date => TO_TIMESTAMP_TZ('2007/10/04 12:03:33.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=DAILY;BYHOUR=01;BYMINUTE=05'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN build_tables.p_cache_orders; END;'
,comments => 'description here'
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'MAX_FAILURES'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'DBSCHEMA.JOB_NAME'
,attribute => 'AUTO_DROP'
,value => TRUE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'DBSCHEMA.JOB_NAME');
END;
/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply