برای انجام زمانبندی در سطح دیتابیس، دو بسته با نامهای dbms_job و dbms_scheduler موجود می باشند که بسته dbms_job از نسخه های قدیمی اوراکل ارائه شده و در نسخه های جدید هم کماکان مورد استفاده قرار می گیرد و بسته dbms_scheduler که در نسخه جدیدتر اوراکل عرضه شد، توانست تا حدود زیادی جایگزین مناسبی برای بسته dbms_job باشد.

دراین متن قصد داریم به بسته dbms_job بپردازیم.

آشنایی با پروسیجرهای مهم DBMS_JOB

این بسته به صورت built-in در دیتابیس موجود است و شامل پروسیجرها و فانکشنهای متنوعی می باشد که در ادامه در مورد هر یک از  آن ها، مطالبی را ارائه خواهیم کرد.

پروسیجر Submit

 این پروسیجر شامل چندین پارامتر می باشد که شرح هر یک به صورت مختصر در ادامه خواهد امد.

PROCEDURE submit (    

job       OUT BINARY_INTEGER,

what      IN  VARCHAR2,

next_date IN  DATE DEFAULT sysdate,

interval  IN  VARCHAR2 DEFAULT ‘null’,

no_parse  IN  BOOLEAN DEFAULT FALSE,

instance  IN  BINARY_INTEGER DEFAULT 0,

force     IN  BOOLEAN DEFAULT FALSE );

پارامتر Job: این پارامتر از نوع output می باشد که شماره job را برمی گرداند. برای اجرا و یا هرگونه تصرفی در job ایجاد شده، باید از این شماره استفاده کرد.

پارامتر what: مقدار ورودی این پارامتر، دستوری است که باید اجرا شود برای مثال، ورودی این پارامتر می تواند نام پروسیجر، فانکشن و … باشد.

مثال:

what  => ‘proc1;’

نکته: اگر دستور ورودی پارامتر what، حاوی تک کوتیشن(‘) باشد، باید این کوتیشن تکرار شود. مثال:

what  => ‘myproc(”10-JAN-99”, next_date, broken);’

what  => usef.emp.give_raise(”JI”, 400);’

پارامتر next_date: این پارامتر در هنگام ساخت job، زمان اولین اجرا را مشخص خواهد کرد و بعد از اولین اجرای جاب، اجرای دفعات بعدی هم توسط این پارامتر مشخص می شود. مقدار پیش فرض این پارامتر برابر با sysdate می باشد.

مثال:

next_date => to_date(’03-05-2017 14:58:18′, ‘dd-mm-yyyy hh24:mi:ss’)

پارامتر interval: این پارامتر مشخص می کند که تناوب زمانی اجرای job در اینده چگونه باشد. مقدار پیش فرض آن برابر با null می باشد.

مثال:

interval => ‘sysdate+1’

نکته: در مورد نحوه تنظیم تاریخ برای پارامترهای مذکور، استفاده از نکات زیر می تواند مفید باشد:

‘SYSDATE + 7’ => هر هفت روز یکبار

‘SYSDATE + 1/48’=>هر نیم ساعت یکبار

‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’ =>هر دوشنبه ساعت 3 بعدازظهر

‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’=>اولین چهارشنبه هر ماه

مثال: نمونه ای از نحوه تعیین مقدار برای پارامتر interval:

variable x number;

execute bms_job.submit(:x,’pack.proc(”arg1”);’,sysdate,’sysdate+1′);

پارامتر no_parse: زمانی که این پارامتر برابر با false تنظیم شده باشد، شی ای که نام ان در قسمت what امده است، یکبار پارس خواهد شد(در هنگام ساخت job) همچنین مقدار true برای این پارامتر، مانع از پارس شدن آن شی خواهد شد.

مثال 1:اگر این پارامتر برابر با TRUE تنظیم شده باشد، با اجرای دستور زیر، خطایی در زمان ایجاد جاب دریافت نخواهیم کرد:

— no_parse=>true

declare

 job NUMBER ;

begin

dbms_job.submit(job => job, what => ‘obj_usef1;’,next_date => to_date(‘03-05-2017 14:58:18’, ‘dd-mm-yyyy hh24:mi:ss’),interval => ‘sysdate+1’,no_parse=>TRUE);

  commit;

end;

/

Done.

همچنین مقدار false برای این پارامتر، مانع از ایجاد جاب خواهد شد:

— no_parse=>false

PLS-00201: identifier ‘OBJ_USEF1’ must be declared

ORA-06512: at “SYS.DBMS_JOB”, line 116

پارامتر Instance: مقدار این پارامتر مشخص می کند که job مورد نظر در کدام یک از instanceها قابل اجرا و اصلاح می باشد. مقدار پیش فرض این پارامتر برابر با dbms_job.any_instance می باشد و می تواند در هر instanceای اجرا شود.

پارامتر force: پارامتر force در این پروسیجر مشخص می کند که شماره تعیین شده برای پارامتر instance باید معتبر باشد یا خیر؟! پس با کمک این پارامتر، اگر شماره instanceای که موجود نیست را به پارامتر instance نسبت دهیم، با خطایی مواجه نخواهیم شد.

در نهایت مثالی را از نحوه ایجاد job از طریق پروسیجر submit را مشاهده خواهید کرد.

مثال:

SQL> VARIABLE jobno number;

SQL>

SQL> BEGIN

  2     DBMS_JOB.SUBMIT(:jobno,

  3        ‘dbms_ddl.analyze_object(”TABLE”, ”USEF”, ”USEF_TBL”,  ”ESTIMATE”, NULL, 55);’ ,

  4        SYSDATE, ‘SYSDATE + 2’);

  5     COMMIT;

  6  END;

  7  /

در ادامه به دیگر پروسیجرهای این پکیج خواهیم پرداخت.

پروسیجر isubmit

 با استفاده از پروسیجر isubmit می توان jobای با شماره دلخواه ایجاد کرد.

  PROCEDURE isubmit    ( job       IN  BINARY_INTEGER,

                         what      IN  VARCHAR2,

                         next_date IN  DATE,

                         interval  IN  VARCHAR2 DEFAULT ‘null’,

                         no_parse  IN  BOOLEAN DEFAULT FALSE);

مثال: در مثال زیر، jobای با شماره 99 ایجاد خواهد شد:

BEGIN

DBMS_JOB.isubmit (

job => 99,

what => ‘usef_proc1(1);’,

next_date => SYSDATE,

interval => ‘SYSDATE + 1/24 /* 1 Hour */’);

COMMIT;

END;

/

پروسیجر remove

 برای حذف یک job باید شماره آن را به تنها ورودی این پروسیجر ارسال کرد البته برای حذف job در حال اجرا، باید ابتدا آن را متوقف نمود و سپس آن را حذف کرد.

PROCEDURE remove    ( job       IN  BINARY_INTEGER );

مثال:

execute dbms_job.remove(95);

commit;

همچنین می توان از بلاک زیر برای حذف همه jobها استفاده کرد:

declare

 job user_jobs.job%TYPE;

 CURSOR c IS  select job from user_jobs;

begin

    OPEN c;

    LOOP

        fetch c into job;

        exit when c%NOTFOUND;

        dbms_output.put_line(‘Removing job: ‘||job);

        dbms_job.remove(job);

    END LOOP;

    CLOSE c;

    commit;

end;

پروسیجر change

 برای تغییر هر کدام از ویژگی های job، می توان از این پروسیجر استفاده کرد که پارامترهای این پروسیجر تقریبا مشابه با پارامترهای پروسیجر submit می باشند.

PROCEDURE change    (job       IN  BINARY_INTEGER,

                        what      IN  VARCHAR2,

                        next_date IN  DATE,

                        interval  IN  VARCHAR2,

                       instance  IN  BINARY_INTEGER DEFAULT NULL,

                       force     IN  BOOLEAN DEFAULT FALSE);

نکته: بعد از هر تغییری باید از commit استفاده کرد.

پروسیجر what

تغییر مقدار پارامتر what برای یک job به خصوص، با کمک این پروسیجر قابل انجام است.

  PROCEDURE what( job       IN  BINARY_INTEGER,  what   IN  VARCHAR2 );

مثال:

BEGIN

DBMS_JOB.WHAT(99, ‘DBMS_DDL.ANALYZE_OBJECT(”TABLE”, ”USEF”, ”USEF_TBL2”,   ”ESTIMATE”, NULL, 50);’);

END;

پروسیجر next_date

 با استفاده از این پروسیجر می توان تاریخ اجرای بعدی job را تغییر داد.

  PROCEDURE next_date ( job IN  BINARY_INTEGER,  next_date IN  DATE     );

پروسیجر interval

 تناوب زمانی که یک job اجرا می شود را می توان با استفاده از این پروسیجر تغییر داد.

 PROCEDURE interval  ( job IN  BINARY_INTEGER, interval  IN  VARCHAR2 );

پروسیجر broken

استفاده از این پروسیجر سبب می شود تا job مربوطه هیچ گاه اجرا نشود. هر job بعد از 16  بار اجرای ناموفق به این حالت در خواهد امد(البته 16 بار اجرای ناموفق متوالی).

 PROCEDURE broken  ( job IN BINARY_INTEGER, broken IN BOOLEAN,next_date IN DATE DEFAULT SYSDATE );

مثال:

exec dbms_job.broken(job => 16,broken => true);

همچنین برای خروج از این حالت می توان زمان  بعدی اجرای آن job را مشخص کرد:

exec dbms_job.broken(job => 16,broken => false,next_date => NEXT_DAY(SYSDATE, ‘MONDAY’));

پروسیجرrun

 برای اجرای job به صورت دستی می توان از این پروسیجر استفاده کرد.

PROCEDURE run       ( job IN  BINARY_INTEGER, force IN  BOOLEAN DEFAULT FALSE);

نکته: حتی اگر job در حالت بروکن هم قرار داشته باشد، از طریق این پروسیجر قابل اجرا می باشد.

مثال:

execute dbms_job.run(14144);

پروسیجر user_export

 با استفاده از این پروسیجر، متن job از طریق شماره آن قابل مشاهده خواهد بود.

PROCEDURE user_export ( job IN BINARY_INTEGER, mycall IN OUT VARCHAR2);

مثال:

SQL> variable x varchar2;

SQL> exec dbms_job.user_export(3,:x);

dbms_job.isubmit(job=>3,what=>’proc1;’,next_date=>to_date(‘2017-05-04:14:15:36′,’YYYY-MM-DD:HH24:MI:SS’) ,interval=>’sysdate+1′, no_parse=>TRUE);

dbms_job و محیط RAC

در محیط RAC این قابلیت وجود دارد تا هر کدام از jobها را به یک instance خاص مقید کرد تا تنها به وسیله آن نود، job مورد نظر قابل اجرا و قابل تغییر باشد.

همانطور که قبلا بیان کردیم، برای تنظیم شماره instance، می توان از پروسیجر submit و یا instance استفاده کرد:

DBMS_JOB.SUBMIT(job,what,interval,no_parse,instance,force);

DBMS_JOB.INSTANCE(JOB,instance,force);

پارامتر force در این دو پروسیجر مشخص می کند که شماره تعیین شده برای پارامتر instance باید معتبر باشد یا نه؟! در صورت تنظیم این پارامتر به مقدار false، شماره instance باید واقعی باشد(به بیانی دیگر، الزاما باید نودی با این شماره وجود داشته باشد) در غیر این صورت، ایجاد job و یا تغییر آن با خطا متوقف خواهد شد البته استفاده از مقدار true، از این خطا جلوگیری خواهد کرد و این پارامتر هر مقدار مثبتی را پذیرا خواهد بود:

–true

SQL> exec dbms_job.instance(job => 16,instance => 50,force => true);

PL/SQL procedure successfully completed

–false

SQL> exec dbms_job.instance(job => 16,instance => 50,force => false);

ORA-23428: job associated instance number 50 is not valid

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86

در این صورت، اگر job شماره 16 به صورت دستی اجرا شود، با خطا متوقف خواهد شد:

SQL> exec dbms_job.run(job => 16);

ORA-23428: job associated instance number 50 is not valid

مگر  آن که از عبارت force استفاده شود که در این صورت این job بر روی نودهای دیگر اجرا خواهد شد:

SQL> exec dbms_job.run(job => 16,force => true);

PL/SQL procedure successfully completed

مقدار پیش فرض این پارامتر برابر با صفر می باشد که به معنی همه نودها خواهد بود و استفاده از مقادیر null و یا منفی برای پارامتر instance سبب بروز خطا خواهد شد.

نکاتی در مورد dbms_job

نکته 1: تنها مالک job می تواند آن را اجرا و یا حذف کند همچنین هرگونه تغییر هم باید توسط owner جاب انجام شود.

نکته 2: شماره job از طریق sequenceای به نام JOBSEQ بدست می اید.

نکته 3: اجرای یک job از طریق job دیگر ممکن نیست.

نکته 4: در صورتی که مقدار پارامتر interval برابر با null تعیین شده باشد، job مورد نظر بعد از اولین اجرا، از لیست job queue خارج خواهد شد و با هر بار اجرای دستی ان، خطای زیر رخ خواهد داد:

ORA-23421: job number 88 is not a job in the job queue

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86

ORA-06512: at “SYS.DBMS_IJOB”, line 781

ORA-06512: at “SYS.DBMS_JOB”, line 267

نکته 5: اطلاعات مربوط به dbms_jobs در جدول $jo$ ذخیره می شود و پروسس CJQ0 که مخفف coordinator job queue می باشد، مسئولیت دارد تا jobهای مربوطه را زیر نظر داشته باشد تا در صورت لزوم به کمک پروسس Jnnn ان job را اجرا کند.

در صورتی که پارامتر job_queue_processes برابر با صفر تنظیم شده باشد، این پروسسها اجرا نخواهند شد. برای بررسی خطاها و هشدارهای مربوط به این پروسس، باید در بین فایلهای تریس، به دنبال فایلی با فرمت SID-cjq0_nnnn.trc گشت.

[root@usef ~]# locate *cjq0*.trc

[root@usef ~]# less /u01/oracle/diag/rdbms/usef11g/usef11g/trace/usef11g_cjq0_11511.trc

نکته 6: از ویوهای مهم مربوط به این نوع از jobها، می توان به dba_jobs و dba_jobs_running اشاره کرد. پرس و جوهای زیر هم می تواند در این زمینه مفید باشد:

پرس و جوی 1:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC  FROM DBA_JOBS_RUNNING r, DBA_JOBS j  WHERE r.JOB = j.JOB;

پرس و جوی 2:

SELECT ‘Job:’|| job, WHAT, ‘Next:’|| to_char(NEXT_DATE,’dd-Mon-yyyy HH24:MI’), ‘ Last:’|| to_char(LAST_DATE,’dd-Mon-yyyy HH24:MI’), ‘ Broken:’|| BROKEN FROM dba_jobs;

نکته 7: در صورتی که فیلد LAST_DATE در ویوی DBA_JOBS برابر با null باشد و یا در صورتی که NEXT_DATE به صورت اتوماتیک در بازه زمانی تعیین شده تغییر نکند، نشانگر عدم اجرای این job به صورت خودکار خواهد بود.

نکته 8:  به صورت خلاصه، برای جلوگیری از عدم اجرای به موقع یک job باید نکات زیر را در نظر داشت:

1.پارامتر JOB_QUEUE_PROCESSES برابر با صفر تنظیم نشده باشد.

SQL> show parameter JOB_QUEUE_PROCESSES

job_queue_processes                  integer     4000

2. پارامتر _SYSTEM_TRIG_ENABLED به مقدار false تنظیم نشده باشد.

select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b where a.indx=b.indx and ksppinm=’_system_trig_enabled’;

برای تنظیم مقدار این پارامتر به true می توان دستور زیر را اجرا کرد:

alter system set “_system_trig_enabled”=TRUE scope=both;

3. بانک اطلاعاتی در حالت RESTRICTED SESSIONS راه اندازی نشده باشد. برای خروج از این حالت، از دستور زیر استفاده می شود:

select logins from v$instance ;

alter system disable restricted session;

4. بلافاصله بعد از اصلاح job از commit استفاده شود.

5. job مورد نظر در حالت broken قرار نگرفته باشد.

select job,broken from dba_jobs where job=<job_number>;

6. ممکن است مقدار پارامتر مخفی job_queue_interval_ به مقداری غیر از مقدار پیش فرض که 5 ثانیه می باشد، تغییر کرده باشد.

select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b where a.indx=b.indx and ksppinm=’_job_queue_interval’;