Monday, December 15, 2008

Running dbms_jobs within a timing interval

If you are using the dbms_job and you want to scheduled the jobs to run at a certain time example from 6 am until 11 pm you can use the following example,

This will run the statspack between 6 am until 11 pm.


dbms_job.submit(:jobid,'if to_char(sysdate,''HH24'') > 5 and to_char(sysdate,''HH24'') <>


To purge the statspack, you can use the following example, statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60);

In prior releases, Statspack identifier tables which contained SQL Text, SQL Execution plans, and Segment identifiers were not purged. It is now possible to purge the unreferenced data in these tables. This is done by requesting the 'extended purge' simply by setting the input parameter i_extended_purge to TRUE when calling the regular purge.

statspack.purge(I_PURGE_BEFORE_DATE => sysdate-60, i_extended_purge=>TRUE);

No comments: