時刻 xx:00 と xx:30 に statspack.snap; を実行するジョブ作成
INTERVAL値に何を設定するかがポイントとなる。
Oracle Databaseリファレンス
11g リリース2(11.2)
B56311-02
DBA_JOBS
http://download.oracle.com/docs/cd/E16338_01/server.112/b56311/statviews_4099.htm#CJAJEHBC
列 データ型 NULL 説明 : : : : INTERVAL VARCHAR2(200) NOT NULL 実行の開始時点で、次のNEXT_DATEを決定するための日付関数
やってみる。★部分がINTERVAL値の設定
variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno , 'statspack.snap;' , SYSDATE+(1/24/60) , 'TRUNC(SYSDATE, ''HH'') + (ROUND(TO_CHAR(SYSDATE, ''MI'')/60, 0)/2 + 0.5)/24' --★ , TRUE , :instno ); commit; end; /
結果は成功(↓)だけれども、計算式が人に優しくないなw
13:40:01 PERFSTAT> select JOB, next_date, LAST_DATE, INTERVAL, WHAT, FAILURES from user_jobs; JOB NEXT_DATE LAST_DATE INTERVAL WHAT FAILURES --- ------------------- ------------------- ------------------------------ --------------- ---------- 86 2011/08/23 14:00:00 2011/08/23 13:33:25 TRUNC(SYSDATE, 'HH') + (ROUND( statspack.snap; 0 TO_CHAR(SYSDATE, 'MI')/60, 0)/ 2 + 0.5)/24 13:40:10 PERFSTAT> 14:00:56 PERFSTAT> select JOB, next_date, LAST_DATE, INTERVAL, WHAT, FAILURES from user_jobs; JOB NEXT_DATE LAST_DATE INTERVAL WHAT FAILURES --- ------------------- ------------------- ------------------------------ --------------- ---------- 86 2011/08/23 14:30:00 2011/08/23 14:00:04 TRUNC(SYSDATE, 'HH') + (ROUND( statspack.snap; 0 TO_CHAR(SYSDATE, 'MI')/60, 0)/ 2 + 0.5)/24
下の方が簡単かな…
variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit( :jobno, 'statspack.snap;', TRUNC(SYSDATE, 'HH'), 'TRUNC(SYSDATE, ''HH'') + (1/24)' , TRUE, :instno ); dbms_job.submit( :jobno, 'statspack.snap;', TRUNC(SYSDATE, 'HH') + (0.5/24), 'TRUNC(SYSDATE, ''HH'') + (1.5/24)', TRUE, :instno ); commit; end; /
14:22:13 PERFSTAT> select JOB, next_date, LAST_DATE, INTERVAL, WHAT, FAILURES from user_jobs; JOB NEXT_DATE LAST_DATE INTERVAL WHAT FAILURES --- ------------------- ------------------- ------------------------------- --------------- ---------- 90 2011/08/23 15:00:00 2011/08/23 14:11:46 TRUNC(SYSDATE, 'HH') + (1/24) statspack.snap; 0 91 2011/08/23 15:30:00 2011/08/23 14:12:42 TRUNC(SYSDATE, 'HH') + (1.5/24) statspack.snap; 0