Menu

26 Haziran 2011 Pazar

Oracle Scheduler

Bu makalemizde Oracle 11g icerisinde olan dbms_scheduler paketini kullanacagiz..

Bunun icin bir user yaratalim ve ona grant create job rolunu verelim..

sql > CREATE USER aslan IDENTIFIED BY aslan;
sql> GRANT DBA TO aslan;
sql > GRANT CREATE JOB TO aslan;

job icinde kullanacagimiz bir table yaratalim...

sql> CREATE TABLE EMP (ID NUMBER, NAME VARCHAR2(30), SAL NUMBER);

tablomuzun icine biraz veri ekledikden sonra job icinde kullanalim..

------------------------------- JOB`un yaratilmiasi------------------
sql > BEGIN
              DBMS_SCHEDULER.CREATE_JOB(
              job_name => 'upt_emp',
              job_type => 'PLSQL_BLOCK',
              job_action => 'update emp set sal = sal * 1.2;',
              start_date => systimestamp,
              repeat_interval => 'FREQ=YEARLY',
             end_date => NULL,
             enabled = TRUE,
             comments => 'Iscilerin maaslarin artirilmasi');
         END;
/

-----------------------JOB`larin listesini gorelim------------------------
SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME                       ENABL  RUN_COUNT
------------------------------ ----- ----------
UPT_EMP                        TRUE           0

-----------------------JOB`un aktiv edilmesi-------------------

 SQL > begin
                   dbms_scheduler.enable(name => 'UPT_EMP');
             end;
             /

-----------------------JOB`un pasif edilmesi-------------------

 SQL > begin
                   dbms_scheduler.disable(name => 'UPT_EMP');
             end;
             /

-----------------------JOB`un kopyalanmasi-------------------

 SQL > begin
                   dbms_scheduler.copy_job('UPT_EMP', 'COP_UPT');
             end;
             /
 //  Bu kodu calistirdikdan sonra, joblarin sayisinin  arttigini goreceksiniz ( ama kopy jobun disable olacaktir).

-----------------------JOB`un calistirilmasi-------------------

 SQL > begin
                   dbms_scheduler.run_job('UPT_EMP',TRUE);
             end;
             /


-----------------------JOB`un durdurulmasi-------------------

 SQL > begin
                   dbms_scheduler.stop_job(job_name => 'UPT_EMP',
                   force => TRUE),
             end;
             /
// Burada job calisiyorsa durdurulucaktir ancak, job calismiyorsa, kod hata verecektir.

-----------------------JOB`un silinmesi-------------------

 SQL > begin
                   dbms_scheduler.drop_job(job_name => 'UPT_EMP',
                   force => TRUE),
             end;
             /

1 yorum: