Jobs are normally run asynchronously under the control of the job coordinator, but they can be controlled manually using the RUN_JOB and STOP_JOB procedures.ĭBMS_n_job (job_name => 'test_full_job_definition',ĭBMS_SCHEDULER.stop_job (job_name => 'test_full_job_definition, test_prog_sched_job_definition') SELECT owner, job_name, enabled FROM dba_scheduler_jobs Job defined by existing schedule and inline program.Ĭomments => 'Job defined by existing schedule and inline program.') Job defined by existing program and inline schedule.Ĭomments => 'Job defined by existing program and inline schedule.') Job_name => 'test_prog_sched_job_definition',Ĭomments => 'Job defined by an existing program and schedule.') Job defined by an existing program and schedule. Job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT'') END ',Ĭomments => 'Job defined entirely by the CREATE JOB procedure.') Job defined entirely by the CREATE JOB procedure. They can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them. Jobs are what the scheduler is all about. They can be defined using the REPEAT_INTERVAL parameter of the CREATE_JOB procedure.
Schedules don't have to be created as separate objects.
This is explained below.Ī schedule can be dropped using the DROP_SCHEDULE procedure.ĭBMS_SCHEDULER.drop_schedule (schedule_name => 'TEST_HOURLY_SCHEDULE') Notice how the interval is defined using the calendaring syntax. SELECT owner, schedule_name FROM dba_scheduler_schedules Repeat_interval => 'freq=hourly byminute=0',Ĭomments => 'Repeats hourly, on the hour, for ever.') Schedules are created using the CREATE_SCHEDULE procedure. Schedules optionally define the start time, end time and interval related to a job. Programs can be deleted using the DROP_PROGRAM procedure.ĭBMS_SCHEDULER.drop_program (program_name => 'test_plsql_block_prog') ĭBMS_SCHEDULER.drop_program (program_name => 'test_stored_procedure_prog') ĭBMS_SCHEDULER.drop_program (program_name => 'test_executable_prog') Notice how programs that accept arguments must have the arguments defined before they can be enabled. SELECT owner, program_name, enabled FROM dba_scheduler_programs Program_action => 'DBMS_STATS.gather_schema_stats',Ĭomments => 'Program to gather SCOTT''s statistics using a stored procedure.') ĭBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog') Program_name => 'test_stored_procedure_prog', Program_action => '/u01/app/oracle/dba/gather_scott_stats.sh',Ĭomments => 'Program to gather SCOTT''s statistics us a shell script.') Program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT'') END ',Ĭomments => 'Program to gather SCOTT''s statistics using a PL/SQL block.')
Programs are created using the CREATE_PROGRAM procedure. A program may related to a PL/SQL block, a stored procedure or an OS executable file. The scheduler allows you to optionally create programs which hold metadata about a task, but no schedule information. Repeat_interval => 'freq=hourly byminute=0 bysecond=0 ', Job_action => 'BEGIN my_job_procedure END ',
If that's all you want, the following example is for you. It is nothing to do with scheduling things to happen on a client PC.Īlthough the scheduler is capable of very complicated schedules, on many occasions you just want to create a simple job with everything defined inline. This is a server-based scheduler, so everything is done in the context of the database server.
0 kommentar(er)
