Rem: This blog is related to send notification for locked users in Oracle database.
Rem: This is test code, and cannot be used in production without proper verification.
Rem: The author take no responsibility of this code.
Rem: Dated 19th September, 2023 - Version 1
Rem: Prepared by Hayat Mohammad Khan
Rem: Contact hayathk@hotmail.com
Rem: This blog is only for knowledge sharing purpose. The concept is taken from google blogs and research community.
Below steps needs to be performed in order.
Step#1: Write procedure code to send notification email for locked users.
create or replace NONEDITIONABLE procedure send_service_account_lock_job as
l_html VARCHAR2(32000);
my_text_msg VARCHAR2(32000);
v_data_exist number(3);
BEGIN
l_html := '';
my_text_msg :='';
v_data_exist :=0;
SELECT count(*) into v_data_exist from dba_users
--where account_status = 'LOCKED' and u in ('Application_Users_Profile');
--once service account is finalized uncomment above
where account_status like '%LOCKED%' and username in ('EM','HR','SCOTT');
if v_data_exist > 0 then
for i in (select username, account_status, lock_date from dba_users where account_status like '%LOCKED%' and username in ('EM','HR','SCOTT')) loop
--for i in (select username, account_status, lock_date from dba_users where account_status like '%LOCKED%' and profile in ('Application_Users_Profile')) loop
my_text_msg := my_text_msg || ' Service Account: ' || i.username || ' is in ' || i.account_status || ' status since ' || to_char(i.lock_date,'DD/MON/RRRR HH24:MI:SS') || chr(13) || chr(10);
my_text_msg := my_text_msg || ' ' || chr(13) || chr(10);
end loop;
my_text_msg := my_text_msg || ' Check on urgent basis ....' || to_char(sysdate,'DD/MON/RRRR HH24:MI:SS') || chr(13) || chr(10);
my_text_msg := my_text_msg || ' From Oracle MOH DB Team ....' || chr(13) || chr(10);
send_mail( p_to => 'to_user@abc.com',
p_from => 'from_user@abc.com',
p_subject => 'Account Lock Status DB: CRM 12c -> crmdb Host:myservname ' ,
p_text_msg => my_text_msg,
p_html_msg => l_html,
p_smtp_host => 'my_smtp_server_IP',
p_cc => 'cc_users_list@abc.com'
);
end if;
END;
Step#2: Send email Procedure Code.
create or replace PROCEDURE send_mail (
p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_html_msg IN VARCHAR2 DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25,
p_cc IN VARCHAR2 DEFAULT NULL
)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
IF p_cc IS NOT NULL THEN
UTL_SMTP.rcpt(l_mail_conn, p_cc);
END IF;
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
IF p_cc IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, 'Cc: ' || p_cc || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_html_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_html_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
Step#2: Configure Job to run after every 10 minutes.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'Job_Lock_Notif_Email',
job_type => 'STORED_PROCEDURE',
job_action => 'send_service_account_lock_job',
number_of_arguments => 0,
start_date => TO_TIMESTAMP_TZ('2023-05-11 14:00:20.000000000 ASIA/BAGHDAD','YYYY-MM-DD HH24:MI:SS.FF TZR'),
repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
end_date => NULL,
enabled => True,
auto_drop => FALSE,
comments => '');
END;
/
BEGIN
DBMS_SCHEDULER.set_attribute( name => 'Job_Lock_Notif_Email', attribute => 'job_action', value => 'send_service_account_lock_job');
DBMS_SCHEDULER.set_attribute( name => 'Job_Lock_Notif_Email', attribute => 'repeat_interval', value => 'FREQ=MINUTELY;INTERVAL=10');
DBMS_SCHEDULER.SET_ATTRIBUTE( name=>'Job_Lock_Notif_Email',attribute => 'RAISE_EVENTS',value=> SYS.DBMS_SCHEDULER.JOB_ALL_EVENTS);
DBMS_SCHEDULER.disable('Job_Lock_Notif_Email');
DBMS_SCHEDULER.enable('Job_Lock_Notif_Email');
END;
/
---------------------------------------------------------------------------
If manual job needs to be run
begin
dbms_scheduler.run_job (job_name => 'Job_Lock_Notif_Email');
end;
/
--OR
execute dbms_scheduler.run_job('Job_Lock_Notif_Email');