Edit Oracle DMBS JOBS

oracle version: oracle 9.2

tools: OEM, sqldeveloper

今天發現oracle上一些排程的時間都莫名其妙的往後延了許多，導致影響其他系統的資料正確性，所以動工將一些固定排程的時間做個修正。

一開始理所當然的打開OEM想直接做編輯(Database => DB_NAME => Distributed => Advanced Replication => Administration => [Tab] DBMS Jobs)。結果... 我猜因為中文語系的關係，導致oracle自己一直無法解析時間參數，丟出error。

其實之前已經某一次要新增排程時就遇過這個問題了，所以只好轉戰sql developer，自己寫PL/SQL = =。不過還是一直有一些細節沒注意到，try了好幾次才ok。

首先要注意的是，一定要是該job的owner才有辦法修改他，就算是dba也不行= =! 以DBA身分查詢"DBA_JOBS"這個view，的確可以看見所有已經在系統內的排程，但若是查詢"USER_JOBS"這個view，會發現沒有東西，以DBA身分去修改屬於別人的JOB時，會遇到"ORA-23421"的錯誤，說明如下:

ORA-23421:	job number string(%s) is not a job in the job queue
Cause:	There is no job visible to the caller with the given job number
Action:	Choose the number of a job visible to the caller

所以，請先確定是用該JOB的owner登入資料庫，此時再去查詢"USER_JOBS"可看見屬於他的排程。

接下來要做的是: "修改"已經存在的排程，這邊不討論新增o刪除。

   1:  begin dbms_job.change(

   2:      job => 777, 

   3:      next_date=>to_date('1-1-2010 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),

   4:      interval=> '/*Each Month*/ Add_months(sysdate, 1)',

   5:      what=>

   6:          '--Your PL/SQL here:

   7:          Declare

   8:              v_year number(4);

   9:              v_month number(2);

  10:              v_day number(2);

  11:          begin

  12:              v_year := to_number(to_char(sysdate,''YYYY''));

  13:              v_month := to_number(to_char(sysdate,''MM''));

  14:              v_day :=to_number(to_char(sysdate,''DD''));

  15:   

  16:              --call pre-defined store procedure

  17:              my_store_procedure(v_year, v_month, v_day);

  18:          end;'

  19:  );

  20:  end;

就先從... "修改"說起吧，如Line #1所見，請使用change。

1. job: job number, user jobs這個view裡的job欄位
2. next_date: 下次執行時間
3. interval: 執行頻率，多久執行一次
4. what: 排程內容(通常是執行某個store procedure，如Line #17)

請注意以上除了job以外，他們的值都有放在一對單引號(' ')裡面!

其餘部分:

Line#4:	/*Each Month*/ 這是註解= =
Line#17:	是此排程最主要要執行的store procedure，但因為他需要其他參數，所以有一些前置作業(Line #12~ Line #14)先來取得這些參數
Line#12:	這邊要特別注意的是!!!! to_char這個函式原本其實只要寫成這樣: to_char(sysdate, 'YYYY') 就好(就是他的format只要放在單引號內)，但是因為這些what語法已經先放在一對單引號內了，如果這邊使用單引號會讓資料庫以為這是what內容的結尾，所以必須在每個單引號前面再加上一個單引號!!! Line #13, Line #14同理

以上，大概是全部要注意的地方了~