Skip to Main Content

Enterprise Manager

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with email alerts

JcaleffaFeb 1 2017 — edited Feb 6 2017

Hello,

I got like 30 databases, and i am using expdp to backup then.

I'm trying to do some HTML or XML template to monitoring the output log.

For example, when all 30 jobs finishes, i need to get the information in one email:

DATABASE | SERVER | STATUS EXPDP

Test               Test              OK / FAIL

test2               test2              OK/FAIL

Is this possible? Can anyone help

Sorry for my bad english, i talk spanish too.

Regards,

Comments

EdStevens

You don't give much to go on. Please copy and paste the text of the entire statement/procedure/script. Please explain in detail how it is being submitted.

User_DDYV1

Due to security issues, I may not be able to copy the entire scripts here. But I'm trying to run a merge statement which is something like below. Usually this statement takes a minute to run and merge the data into the dimension table, but every first of month it runs forever and hangs there.
merge into table d using
( select * from (
select key , id , id1 , sysdate , row_number() over (partition by key order by v.id) as row_num
from f_transaction f,
(select ID from f_import v where date = 'Today'sDate' ) v
where f.id = v.id
and f.date = 'today's date' )
where row_num = 1 ) x
on (d_key = x.key
when matched then update set
d.id = x.id
, d.dw_update_date = x.dw_update_date
when not matched then
insert ( id , Key, Date)
values ( x.id , x.key , x.date);

When running this merge statement in toad, it executes till 23 seconds, then it stays there and stops counting. Toad then get hanged. I checked in the session monitor for any locks and made sure no other jobs are running parallelly.
So need some help here to know how to troubleshoot this.

JohnWatson2

And if you run the statement from SQL*Plus?

tsangsir

And are the numbers of rows inf_transaction and f_import more or less the same throughout the month?

User_DDYV1

@johnwatson2 , The same issue with SQL plus as well.
@tsangsir , the rows gets added each day , so these two tables keeps growing. These two tables already have millions of records, we do have index on these tables as well.

JohnWatson2

JohnWatson2 , The same issue with SQL plus as well.
Do you mean that it runs to completion in 23 seconds? Or do you mean that it hangs forever?
A simple way to gather a bit more information, in SQL*Plus, would be to

set timing on
set autotrace on
merge into......

and copy/paste it all here.

EdStevens

and copy/paste it all here.
And please be sure to post it as code-formatted

User_DDYV1

@johnwatson2 , @edstevens1 Thanks , when I run the same query in SQL plus, it hangs there as well.
the thing is, I ran it today, the same merge statement and I got the result in 23 seconds. But I'm facing this issue only during the first of month (Every time).

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 6 2017
Added on Feb 1 2017
4 comments
428 views