dbms_job實驗

做statspack實驗的時候發現一個問題，就是設定dbms_job後，不自動進行刷新，記錄一下，備忘

在oracle的$ORACLE_HOME/rdbms/admin下有一個腳本spauto.sql看名字就知道是自動做statspack的。其中有一段腳本可以直接來用

--
--  Schedule a snapshot to be run on this instance every hour, on the hour
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/

創建一個user job

SQL>
SQL>
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);tno);
  commit;
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL>

SQL> select job, last_date, this_date, next_date from user_jobs;
       JOB LAST_DATE           THIS_DATE           NEXT_DATE
---------- ------------------- ------------------- -------------------
        21                                         2010-02-25 16:00:00
1 row selected.
SQL>

因為是實驗所以將間隔設定的為1min一次

SQL> exec dbms_job.interval(21, 'sysdate+1/(24*60)');
PL/SQL procedure successfully completed.
SQL>

SQL> commit;
Commit complete.

SQL> col interval for a30
SQL> select job, last_date, this_date, next_date , interval from user_jobs;
       JOB LAST_DATE           THIS_DATE           NEXT_DATE           INTERVAL
---------- ------------------- ------------------- ------------------- ------------------------------
        21                                         2010-02-25 16:00:00 sysdate+1/(24*60)
1 row selected.
SQL>

可以看到已經設置成功了。

但是等了1min發現沒有新的snap記錄

SQL> select count(*) from stats$snapshot;
  COUNT(*)
----------
         3
1 row selected.

感覺應該是next_date這個字段有影響

執行

SQL> exec dbms_job.next_date(21, to_date('20100225 15:45:01', 'yyyymmdd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

 

SQL> select sysdate from dual;
SYSDATE
-------------------
2010-02-25 15:45:10
1 row selected.

 

SQL> select job, last_date, this_date, next_date , interval from user_jobs;
       JOB LAST_DATE           THIS_DATE           NEXT_DATE           INTERVAL
---------- ------------------- ------------------- ------------------- ------------------------------
        21 2010-02-25 15:45:05                     2010-02-25 15:46:05 sysdate+1/(24*60)
1 row selected.

注意已經發生變化了

SQL> select count(*) from stats$snapshot;
  COUNT(*)
----------
        4
1 row selected.

等1min左右

SQL> select count(*) from stats$snapshot;
  COUNT(*)
----------
        5
1 row selected.

記錄一下，備忘。