做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.
記錄一下，備忘。
博文
