Scops
Docs Download

Oracle problems

ORA-28002: The Password Will Expire. Which Accounts Expire When, and the Fix Without an Outage

3 November 2026

An application account was created five months ago with the DEFAULT profile, nobody changed the profile, and the password has a date. For seven days every logon carries a warning that the connection pool does not read. On the eighth day the logon fails, at whatever hour the pool next reconnects. Every account's date is in the dictionary today.

What the error says

ORA-28002: the password will expire within 6 days is a warning, and the logon succeeds. It comes from the profile of the user: PASSWORD_LIFE_TIME sets how long a password lives, PASSWORD_GRACE_TIME how many days of warnings follow. Both default to 180 and 7 days when a profile omits them, and the DEFAULT profile that comes with the database uses those values. After the grace period the message is ORA-28001: the password has expired and connections are rejected.

Two neighbours of the error:

Which accounts expire, and when

The date is in DBA_USERS. The filter leaves out Oracle's own accounts and the ones locked on purpose:

SELECT u.username, u.account_status, u.profile,
       u.expiry_date,
       ROUND(u.expiry_date - SYSDATE)   AS days_left,
       u.last_login
FROM   dba_users u
WHERE  u.oracle_maintained = 'N'
AND    u.account_status NOT LIKE '%LOCKED%'
AND    u.expiry_date IS NOT NULL
ORDER  BY u.expiry_date;

ORACLE_MAINTAINED and LAST_LOGIN exist from 12c; on 11g drop those two columns. A status of EXPIRED(GRACE) means the warnings have started; EXPIRED means the logons already fail. The limits behind the dates:

SELECT p.profile, p.resource_name, p.limit
FROM   dba_profiles p
WHERE  p.resource_name IN ('PASSWORD_LIFE_TIME',
                           'PASSWORD_GRACE_TIME',
                           'PASSWORD_ROLLOVER_TIME',
                           'FAILED_LOGIN_ATTEMPTS')
ORDER  BY p.profile, p.resource_name;

The fix, in order

  1. An account in grace: reset the password before the date. ALTER USER app IDENTIFIED BY "the-same-password"; resets the expiry date and changes nothing for the application, as long as the profile does not limit reuse (the DEFAULT profile does not). If nobody knows the password, the hash from SYS.USER$ can be set back with IDENTIFIED BY VALUES; that clause is undocumented, so try it on a test database first.
  2. An account that must change its password: do it without an outage. With PASSWORD_ROLLOVER_TIME in the profile (19c and later), the old and the new password both work for the rollover period, up to 60 days, and the account shows IN ROLLOVER. Change the password, then update the pools one by one.
  3. Application accounts: a profile of their own. CREATE PROFILE app_accounts LIMIT PASSWORD_LIFE_TIME UNLIMITED; or a lifetime that matches the rotation calendar, then ALTER USER app PROFILE app_accounts;. A new profile does not revive a date that has already passed: reset the password as well.
  4. Already locked (ORA-28000): unlock, then find the pool with the old password. ALTER USER app ACCOUNT UNLOCK; holds only until the next wrong attempt. DBA_AUDIT_TRAIL or the unified audit trail names the machine that keeps failing.

See it coming

The date is known 180 days ahead, and the first query gives it. Radar Engine, the detection layer of Scops, runs that check every day on every database, including the account Scops itself connects with:

DetectorClockWhen it fires
Password expiryDaily, on every account that is not Oracle-maintained and not permanently locked30 days left: Watch. 14 days: Warning. 3 days: Critical. An expired password stays a finding for 30 days.
Logon stormLive, every 5 minutesPools that reconnect at once, the shape of an account that just expired or locked.

A Critical with three days left arrives by mail at once, so the reset lands in a working day and not in the night the pool restarts.

Questions

Does ORA-28002 stop the logon?
No. It is a warning for the length of the grace period, 7 days by default, and the logon succeeds. After the grace period the error is ORA-28001 and the logon fails.

Can I turn expiry off for one account?
Yes: a profile with PASSWORD_LIFE_TIME UNLIMITED, assigned to that user. It does not un-expire a date that has already passed; reset the password too.

Why did the account lock as well?
FAILED_LOGIN_ATTEMPTS, 10 in the DEFAULT profile. One pool that still has the old password locks the account for all the others. Unlock it, then fix that pool.

How do I change an application password without downtime?
Gradual password rollover: set PASSWORD_ROLLOVER_TIME in the profile, change the password, and both the old and the new one work until the period ends, up to 60 days. Update the pools one at a time.

Related problems

Sources