script to remove OEM jobs and events

[jchamp02155]

Currently there is no automated process to remove OEM jobs and events in history so I am forced to remember to periodically delete old jobs and events.

Does anyone have a script which does this?

I have created an enhancement request but in the meantime I want to automate the process.

Thanks.

Joe Armstrong-Champ
DBA
Tufts University
joseph.armstrong-champ@tufts.edu

[jerre]

Hi,

I wrote the following script to do just that for our oem server.

!!PLEASE USE THIS SCRIPT AT YOUR OWN RISK!!

It might be totally wrong ... but it seems to work for me ...

You have to pass in the number of most recent days you want to keep in the oem log.

eg : exec oem.removeoemjobhistory(5);

You can off course put this in a daily scheduled oem job ...

Let me know what you think!

Cheers,

Jerre
__________________________________________________ ___

CREATE OR REPLACE PROCEDURE "OEM"."REMOVEOEMJOBHISTORY" (nDays
IN number)
IS

nOutput_id number ;
nDate date := sysdate - nDays ;
nTotal number := 0 ;

CURSOR output_id_cursor
IS
SELECT output_id
FROM OEM.SMP_VDJ_JOB_LOG
WHERE time_stamp < nDate;

BEGIN

dbms_output.put_line('All Job History from before ' || nDate || ' will be deleted');

OPEN output_id_cursor;
LOOP

FETCH output_id_cursor INTO nOutput_id;

EXIT WHEN output_id_cursor%NOTFOUND;

DELETE FROM SMP_VDJ_JOB_OUTPUT
WHERE blob_id = nOutput_id;

END LOOP;

CLOSE output_id_cursor;

DELETE FROM OEM.SMP_VDJ_JOB_LOG
WHERE time_stamp < nDate;

COMMIT;

END;