Skip to content

Instantly share code, notes, and snippets.

@devjason
Created December 11, 2013 18:16
Show Gist options
  • Save devjason/7915595 to your computer and use it in GitHub Desktop.
Save devjason/7915595 to your computer and use it in GitHub Desktop.
Script suitable for cron to check Oracle password expiration status
#!/bin/sh
# Displays Oracle user password expiration status and date.
# Open but expired date users have not logged in since they expired.
# Where filter currently commented out, use as desired.
# Source: http://oraclespin.wordpress.com/2008/01/30/check-oracle-password-for-expiration/
TOADDR=someone@example.com
REPORT=`mktemp`
TODAY=`date +%F`
sqlplus -S / as sysdba <<EOF > $REPORT
set linesize 200
set pagesize 500
column "EXPIRE DATE" format a20
select
username as "USER NAME",
expiry_date as "EXPIRE DATE",
account_status
from
dba_users
-- where
-- expiry_date < sysdate+120
-- and account_status IN ( "OPEN", "EXPIRED(GRACE)" )
order by
account_status, expiry_date, username
/
EOF
mail -s "Oracle Password Status: $TODAY" $TOADDR < $REPORT
rm $REPORT
@barathku
Copy link

Does it work for anyone?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment