记一次statspack错误处理

客户数据库警告日志显示，经检查是由statspack自动调度脚本引起的。
[quote]Thu Jul 22 12:30:14 2010
Errors in file /oracle/admin/fysb/bdump/fysb_j000_1805.trc:
ORA-12012: error on auto execute of job 82
ORA-12005: may not schedule automatic refresh for times in the past[/quote]
将其job删除
[quote]exec dbms_job.remove('82');[/quote]
再次执行spauto.sql
[quote]SQL> @spauto.sql
begin
*
ERROR at line 1:
ORA-23420: interval must evaluate to a time in the future
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at line 3[/quote]

解决办法
[quote]begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')',TRUE, :instno);
commit;
end;
/
[/quote]
顺便将统计级别从默认的5，提高至6，用于SQL执行计划分析
[quote]exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');[/quote]