I get requests for password changes frequently.
I would try to figure out a password, change the password, and email the password to the user.
This has become a bit cumbersome.
CREATE OR REPLACE PROCEDURE change_pw (pi_user VARCHAR2)
IS
c_profile CONSTANT VARCHAR2(30) := 'PROFILE_NAME';
c_domain CONSTANT VARCHAR2(30) := '@domain.com';
c_sender CONSTANT VARCHAR2(50) := SYS_CONTEXT ('USERENV', 'DB_NAME')||c_domain;
c_recipients CONSTANT VARCHAR2(30) := REGEXP_SUBSTR(pi_user,'[^_]+',1,2)||c_domain;
c_cc CONSTANT VARCHAR2(30) := 'dba'||c_domain;
c_subject CONSTANT VARCHAR2(50) := 'Password Changed For: '||pi_user;
c_pw CONSTANT VARCHAR2(10) := DBMS_RANDOM.STRING ('A', 9) || TRUNC (DBMS_RANDOM.VALUE (0, 10));
c_message CONSTANT VARCHAR2(50) := 'New Password: '||c_pw;
l_sql VARCHAR2(100);
BEGIN
FOR x IN (
SELECT u.name
FROM sys.user$ u, sys.profname$ p
WHERE u.resource$ = p.profile# AND p.name = c_profile
AND u.name=UPPER(pi_user))
LOOP
l_sql := 'ALTER USER '|| x.name || ' IDENTIFIED BY ' || c_pw || ' ACCOUNT UNLOCK PASSWORD EXPIRE';
EXECUTE IMMEDIATE(l_sql);
UTL_MAIL.SEND(sender=>c_sender,recipients=>c_recipients,cc=>c_cc,subject=>c_subject,message=>c_message);
END LOOP;
END;
/
The above stored procedure allows me to create a random password and emails the user.
You can find article – How To Send Email From 10g Oracle Database (UTL_MAIL) – open in new window
Thinking Out Loud 