Reset Of Kept Packages

The command for submitting the redo_pin procedure to execute once per day would be:

VARIABLE x NUMBER;

EXECUTE DBMS_JOB.SUBMIT(:x,’BEGIN DBMS_REVEALNET.REDO_PIN; END;’, SYSDATE,’TRUNC(SYSDATE)+1+(15/1440)’);

In the submit shown for the redo_pin procedure notice the interval: ’TRUNC(SYSDATE)+1+(15/1440)’ this translates to every morning at 15 minutes after midnight, precisely. By using the TRUNC function we strip off all time information from the SYSDATE value and then add 1 day and 15 minutes. The same interval could have been accomplished by adding 1455/1440 to the truncated SYSDATE, but by showing both steps it makes it clearer what is being accomplished.

If desired, the redo_pin procedure could be enhanced by adding inserts into the Remote DBA_RUNNING_STATS table to reflect the actions it accomplishes.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm