Oracle DDL trigger

Dear all,

right now reading:

http://www.dba-oracle.com/t_ddl_triggers.htm

anyway to make the DDL change trigger alert comes out in email format instead of report? this make the world much easlier,

The whole script here will be better.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RindbaekSenior ConsultantCommented:
You could use the UTL_MAIL package in stead of the insert into....
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_mail.htm
0
Mark GeerlingsDatabase AdministratorCommented:
"this make the world much easier,"

Are you sure?  It looks to me like having an Oracle DDL trigger send an e-mail makes your database world more complex.  Oracle is a database system, so it does tasks inside the database very well and consistently.  Asking an Oracle process though to send an e-mail is different.  This depends on a link to a system outside of the database.  The comment from Rindbaek shows you what you need to install in Oracle to support this, if you want to use this.

What would you want the trigger to do though if your e-mail server is down when someone does a DDL event in the database?  Or, what happens if the network connection between your database server and e-mail server is down?  Or, what happens when someone quits using an e-mail address that they used to use?

If you simply have the DDl trigger log this activity in an audit table that you create for this purpose, you can easily set up a report and the report could be delivered via e-mail.  This report can be run whenever you need it, and repeated or rerun if necessary.  This gives you flexibility and reliability that you won't get if you try to have a DDL trigger send email directly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"What would you want the trigger to do though if your e-mail server is down when someone does a DDL event in the database?  Or, what happens if the network connection between your database server and e-mail server is down?  Or, what happens when someone quits using an e-mail address that they used to use?
"
This is a good point and we have a good reason to have HA/DR for the email system. email system can't down in the real world. Even not because of a database server want to use it.

if the email server down, the alert by this trigger has to send again.

" This report can be run whenever you need it, and repeated or rerun if necessary."

by what tools I need to have this feature ? one more tools to manage and setup ?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark GeerlingsDatabase AdministratorCommented:
"if the email server [is] down, the alert by this trigger has to send again."

Oracle triggers do not include a "retry" option.  They either succeed, or they fail.  Do you want a possible e-mail failure to prevent the DDL action from completing?

"more tools to manage and setup?"

If you did not have an Oracle database in the past, but now you have an Oracle database to manage, then yes, you will have more tools to manage.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Oracle triggers do not include a "retry" option.  They either succeed, or they fail.  Do you want a possible e-mail failure to prevent the DDL action from completing?"

good point, I don't have, then email server HA/DR. a lot of company has the email server HA/DR

"If you did not have an Oracle database in the past, but now you have an Oracle database to manage, then yes, you will have more tools to manage. "

I mean reporting tools.
0
RindbaekSenior ConsultantCommented:
im not quite sure what you want. But if you want to use a centralized management and reporting tools on your oracle databases you could use Oracle Enterprise Manager Grid/Cloud control, latest version is 12C.

You could then write from the ddl change trigger to a table and create a custom metric that checks the table every x minutes. This will raise an alert in the grid/cloud control that based on your preferences also can be send as an email to whoom it may concern.

But its not a over the night setup to install and configure EOM 12C, and if you only have a single database its certainly overkill.  I think you could do the same in the OEM console (management tool for single instances).


A fast solution could be to create the trigger where it writes to a table and then use dbms_scheduler to run a check every x minutes against the table, that could send an email using utl_mail or one of the other packages for mail communication i think its dbms_smtp.
 
But as markgeer states there is no guarantee that the mail is send, that depends on the mail server setup.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Rindbaek,

"But as markgeer states there is no guarantee that the mail is send, that depends on the mail server setup.
"
yes, but it also means that this is the same as no one 's email can guarantee can sent to the email server, then how we solve it?

network, PC's LAN card, email server and target PC's network ? very hard to said, everything can happen ..... agree?

as I come from MS SQL server world and I implemented this already, trigger and use the MS SQL process to send email, then simple and just one script.

Then very easy to troubelshoot /enable/disalbe and change too !

"A fast solution could be to create the trigger where it writes to a table and then use dbms_scheduler to run a check every x minutes against the table, that could send an email using utl_mail or one of the other packages for mail communication i think its dbms_smtp."

good, then what looks to trigger, can you give  an example ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
RindbaekSenior ConsultantCommented:
The reason for using the trigger to write to the table is that you have the history, incase your email system fails. You can the use a scheduled task to check if there is any new entries in the table since the last run. You can then send an email alert if there is.

The trigger could be the one you started to look at (http://www.dba-oracle.com/t_ddl_triggers.htm).

The check could be an annonymous PL/SQL block or a stored procedure that checks the table and and send an email using UTL_MAIL if it finds any error (http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_mail.htm).

And the task mechanism cold be the DBMS_SCHEDULER (http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm)
0
marrowyungSenior Technical architecture (Data)Author Commented:
"The reason for using the trigger to write to the table is that you have the history, incase your email system fails. You can the use a scheduled task to check if there is any new entries in the table since the last run. You can then send an email alert if there is.

The trigger could be the one you started to look at (http://www.dba-oracle.com/t_ddl_triggers.htm)."

yeah, I agree that it is good and we can do the SELECT with where cause, right?

"The check could be an annonymous "

not good to use you mean? just an email..

"DBMS_SCHEDULER"

command mode doable or script mode ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
markgeer, any idea?
0
Mark GeerlingsDatabase AdministratorCommented:
Any ideas on what?  Are you still looking for help getting an Oracle trigger to send e-mail?  Or, are you considering other options (like a report) to see a history of DDL activity?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"The check could be an annonymous "

not good to use you mean? just an email..

"DBMS_SCHEDULER"

command mode doable or script mode ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.