oracle 11g monitor user lock and email alert

I need a script to monitor a user in oracle 11g - if the account is locked I need to send an email
not sure how to put an email clause in a monitoring script
LVL 1
bkreynolds48Asked:
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.

slightwv (䄆 Netminder) Commented:
For simple emails I use UTL_MAIL.  It requires a little setup but it's what I use.

There is also UTL_SMTP.  An Expert here has some good articles on using it:
http://www.experts-exchange.com/Database/Oracle/A_5915-Extending-Oracle's-Email-functionality-with-PL-SQL-Authentication.html

As far as the rest of the script goes, do you already have something and are just looking for the email portion?

If so, post what you have.
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
bkreynolds48Author Commented:
I just needed the email portion

Thanks
0
bkreynolds48Author Commented:
can I ask another question?
Can I run a query with email output from dbms_job
say
select user_id, account_status, lock_date from dba_users where username = 'BEV' and account_status = 'LOCKED';
0
slightwv (䄆 Netminder) Commented:
>>Can I run a query with email output from dbms_job

Sure.  You just need PL/SQL to loop through the results of the select and build the message body in a CLOB or varchar2 string variable.

Then email the results.
0
slightwv (䄆 Netminder) Commented:
Once you have utl_mail set up properly, here's a very quick example.

declare
	crlf			varchar2(2):= CHR( 13 ) || CHR( 10 );
	message_v		varchar2(32000);

begin
	for i in (select user_id, account_status, lock_date from dba_users where username = 'BEV' and account_status = 'LOCKED') loop
		message_v := message_v || i.user_id || ' is in ' || i.account_status || ' status since ' || to_char(i.lock_date,'MM/DD/YYYY') || crlf;
	end loop;
	utl_mail.send(
		sender => 'database@somedomain.com',
		recipients => 'someuser@somedomain.com',
		subject => 'Locked Accounts',
		message => message_v
	);
end;
/

Open in new window

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.