Password expiring Alert

Following script will help you to get an alert if users have password longer than certain days. Lets say you set an alert for password to expire in 180 days and you want to sent an alert after 150 days:

. .env

cd /tmp
sqlplus -S /nolog <<>
connect apps/$APPSPASS
set feedback off
set pages 100
set lines 100
col "username" format a15
col "account_status" format a15
col "profile" format a15
col "LAST_PASSWD_DATE" format a16
col "CHANGE BY" format a16
spool /tmp/userpassword.log

select /*+ RULE */ dd.username,dd.account_status,dd.profile,
dd.created "CREATION_DATE", u.ptime "LAST_PASSWD_DATE",u.ptime+180 "CHANGE BY"
from dba_users dd, sys.user$ u
where dd.USERNAME = u.NAME
and u.ptime <>
and dd.account_status = 'OPEN'
and dd.USERNAME != 'APPLSYSPUB' -- Exception as this account should have the default password
order by dd.created,u.username
/
spool off
EOF
if [ -s /tmp/userpassword.log ] ; then
cat /tmp/userpassword.log mailx -s "Users with Password older than 150 days" appsdbafreelance@gmail.com 
fi


Apps Dba Free Lance