RE: Deleting old job run entries in OEM 10g
- From: "Reardon, Bruce \(CALBBAY\)" <Bruce.Reardon@xxxxxxxxxxxxxxxxxxxxxxx>
- To: <ravi.madabhushanam@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 24 May 2006 10:53:02 +1000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]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.
--
//www.freelists.org/webpage/oracle-l
Other related posts: