RE: Deleting old job run entries in OEM 10g

I'm sure this is unsupported, but in OEM 2.2 something like the following would work:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
    CURSOR c_job IS

      SELECT j.job_id, j.job_name, j.owner, j.description,h1.exec_num, jpt.target_name
      FROM   smp_vdj_job j,smp_vdj_job_per_target jpt,
             (SELECT job_id, target_name, exec_num, MIN(time_stamp+time_zone/86400000) "TIMESTAMP"
              FROM   smp_vdj_job_log 
              WHERE  time_stamp+time_zone/86400000 < SYSDATE
              AND  status = 4 
              GROUP BY job_id, target_name, exec_num) h1
      WHERE  j.job_id = jpt.job_id
        AND  j.job_id = h1.job_id
        AND  jpt.target_name = h1.target_name
        AND  j.job_name LIKE '%(no output)%';

  BEGIN     FOR c_job_rec IN c_job LOOP
    BEGIN
       DBMS_OUTPUT.PUT_LINE('Deleting output for; job name: '||c_job_rec.job_name||', id: '||c_job_rec.job_id);

       DELETE FROM smp_vdj_job_output
         WHERE blob_id IN (
      SELECT output_id
      FROM   smp_vdj_job_log
      WHERE  job_id      = c_job_rec.job_id
      AND  target_name = c_job_rec.target_name
      AND  exec_num    = c_job_rec.exec_num
     );
 
       DELETE FROM smp_vdj_job_log
         WHERE job_id      = c_job_rec.job_id
           AND target_name = c_job_rec.target_name
           AND exec_num    = c_job_rec.exec_num;
 
       DELETE FROM smp_vdm_notification_details
         WHERE name        = c_job_rec.job_id
           AND execnum     = c_job_rec.exec_num
           AND target      = c_job_rec.target_name
           AND type        = 1;

    END;
    END LOOP;
    COMMIT;  END;
/

HTH,
Bruce Reardon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ravi Madabhushanam Sent: Wednesday, 24 May 2006 10:43 AM

We are using OEM Grid control to monitor 4 databases. We have configured many jobs on these databases to take backup, monitor the status of DB and mail etc..

In this,there is a job, which executes for every 5mins. (appx 275 times a day). The OEM repository is preserving the entries of this job for last 31 days . I'm able to see first 500 entries of this particular job ( at max last 2 days).

actually thease entries are not of that important for us unless there is a failure of job or so. even in that case at max we would like to keep only last 7 days information. I read in docs that we can configure OEM repository to how long the information should be kept with. but its not specifically specified for jobs/for a particular job.

Now can anyone please tell me, will i be able to delete this information from repository periodically to decrease the space usage .if yes, how?.

Thanks&Regards,
Ravi.M

NOTICE
This e-mail and any attachments are private and confidential and may contain privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed.

--
http://www.freelists.org/webpage/oracle-l