I’m using Oracle 11g and was really confused on how the password expiration date was set (default values).
To retrieve the default password expiration values set for all new users run this query:
select LIMIT, RESOURCE_NAME
from dba_profiles
where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME');
Your output should look something like this:
LIMIT RESOURCE_NAME ---------------------------------------- -------------------------------- 180 PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX 7 PASSWORD_GRACE_TIME
To find all users that have a password expiring in the next 120 days:
set pagesize 500 set linesize 200 set trimspool on 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
To List all users and their expire date + current status:
select username, expiry_date, account_status from dba_users;