//
you're reading...
Database, IT-Center, Oracle

Oracle Job Schedule (Run Procedure with schedule)

Example Oracle job schedule :

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name          =>  'bt_auto_schedule',
job_type          =>  'STORED_PROCEDURE',
job_action        =>  'bt_job',--procedure name
start_date        =>  TO_TIMESTAMP_TZ('30-DEC-2008
00:00:00','DD-MM-YYYY HH24:MI:SS'),
repeat_interval   =>  'FREQ = DAILY; INTERVAL = 1');
END;
/

BEGIN
dbms_scheduler.enable('bt_auto_schedule');
END;
/



 — List all existing jobs:
SELECT * FROM all_scheduler_jobs;

— Disable temporary a job:
BEGIN
dbms_scheduler.disable(‘populate_nodes’, TRUE);
END;
/
_______________________________________________________________

Jobs Tutorial
from : http://www.oracle-base.com/articles/10g/Scheduler10g.php

Jobs are what the scheduler is all about. 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.

-- Create jobs.
BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');

  -- Job defined by an existing program and schedule.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_job_definition',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule.');

  -- Job defined by existing program and inline schedule.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_prog_job_definition',
    program_name    => 'test_plsql_block_prog',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined by existing program and inline schedule.');

  -- Job defined by existing schedule and inline program.
  DBMS_SCHEDULER.create_job (
     job_name      => 'test_sched_job_definition',
     schedule_name => 'test_hourly_schedule',
     job_type      => 'PLSQL_BLOCK',
     job_action    => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
     enabled       => TRUE,
     comments      => 'Job defined by existing schedule and inline program.');
END;
/

PL/SQL procedure successfully completed.

-- Display job details.
SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

OWNER                          JOB_NAME                       ENABL
------------------------------ ------------------------------ -----
SYS                            PURGE_LOG                      TRUE
SYS                            GATHER_STATS_JOB               TRUE
SYS                            TEST_FULL_JOB_DEFINITION       TRUE
SYS                            TEST_PROG_SCHED_JOB_DEFINITION TRUE
SYS                            TEST_PROG_JOB_DEFINITION       TRUE
SYS                            TEST_SCHED_JOB_DEFINITION      TRUE

6 rows selected.

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.

BEGIN
  -- Run job synchronously.
  DBMS_SCHEDULER.run_job (job_name            => 'test_full_job_definition',
                          use_current_session => FALSE);

  -- Stop jobs.
  DBMS_SCHEDULER.stop_job (job_name => 'test_full_job_definition, test_prog_sched_job_definition');
END;
/

Jobs can be deleted using the DROP_JOB procedure.

BEGIN
  DBMS_SCHEDULER.drop_job (job_name => 'test_full_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_prog_sched_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_prog_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_sched_job_definition');
END;
/

PL/SQL procedure successfully completed.

-- Display job details.
SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

OWNER                          JOB_NAME                       ENABL
------------------------------ ------------------------------ -----
SYS                            PURGE_LOG                      TRUE
SYS                            GATHER_STATS_JOB               TRUE

2 rows selected.

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: