HowTo: List locked, expired and to expire dates for Oracle logins query for sqlplus/scripting

Oracle

set pagesize 5000
set linesize 999
set trimspool on

column "Expire Date" format a20
column "Locked Date" format a20

select 
    gn.GLOBAL_NAME as "Instance",
    du.username, 
    du.expiry_date as "Expire Date",
    du.account_status,
    du.lock_date as "Locked Date",
    du.profile,
    dp.limit as "Profile Password Expiration"
from dba_users du, global_name gn, dba_profiles dp
where (du.account_status IN ( 'OPEN', 'LOCKED' )  OR du.account_status like '%EXPIRE%')
  and du.profile = dp.profile
  and dp.resource_name = 'PASSWORD_LIFE_TIME'
order by du.account_status, du.expiry_date, du.username

Updated to show which login profile is being by a user and what the password expiration interval is per the login profile.

2 Replies to “HowTo: List locked, expired and to expire dates for Oracle logins query for sqlplus/scripting”

  1. Ed Steven’s wrote:

    Exploring password lifetime and grace period

    Some time back I began to get an odd complaint from some of my end users. It seems that when they connected to the Oracle database, they received a warning that their password was about to expire, and yet never – even after the password grace period had passed – received a prompt to change their password.

    Normal and expected behavior would have been for them to start receiving this message when they reached their expiry date and continue to receive it until they reached the end of the grace period, at which time they would be forced to enter a new password. So why were they never prompted/forced for a new password? A check of a typical account showed they had an EXPIRY_DATE of null. I thought this was odd, but being pressed for time, simply expired the account (ALTER USER joe ACCOUNT EXPIRE;), had the user change passwords, and checked that they had a new, valid EXPIRY_DATE. Thinking I had the fix, I ran a script to expire all users with an EXPIRY_DATE of null, and moved on.

    read more on Ed’s blog

Leave a Reply

Your email address will not be published. Required fields are marked *