From the last update of Bruno you can also do the following: it's good to have a check on which users are potential candidates based on the filters already supplied (and avoid hardcoded values where possible), but you should also run queries to find which accounts have large password life times … this for as much as it is not visible from for example the above query, but typically you can create another query to put focus on actual settings of the password life time value itself (ignoring when last actual password reset), so you'd have a query with a different sorting column as here you're looking for accounts that have abnormal (high) or no password life time.

( I couldn' t resist and I've done your homework ;-)

SELECT u.username
     , u.password_change_date

     , u.profile

     , NVL( TO_CHAR( u.password_change_date + TO_NUMBER( p.limit DEFAULT NULL ON CONVERSION ERROR ) ), 'never' )

     , ROUND( MONTHS_BETWEEN( u.password_change_date + TO_NUMBER( p.limit DEFAULT NULL ON CONVERSION ERROR )

                            , sysdate )
            ) months

  FROM dba_users u

  INNER JOIN dba_profiles p

    ON    p.profile = u.profile

      AND p.resource_name = 'PASSWORD_LIFE_TIME'

  [WHERE …]

;

Best regards,

Bruno

Hello Charles,

simpler: ADD_MONTHS( password_change_date, 12 ) but maybe you should go to some more complexity than "add 1 year"; indeed, is this one year sure for any user? You might join with DBA_PROFILES to see the actual limit assigned to each user (and take into account the possibility of value "UNLIMITED")

Best regards,

Bruno Vroman.