Close Menu
Oracle

Oracle ACCOUNT_STATUS: What Each Value Means

HenriqueBy 10 Mins Read

This post is also available in: Português (Portuguese (Brazil))

Your application stops connecting and the error is blunt:

ORA-28000: the account is locked

or:

ORA-28001: the password has expired

Before you start firing off ALTER USER, the first step is to find out the account’s real state. The ACCOUNT_STATUS column in the DBA_USERS view answers that. It tells you whether the account is open, locked, has an expired password, or some combination of those. Each state needs a different action, and unlocking blindly only delays the problem.

How to check the status

SELECT username, account_status
FROM   dba_users
WHERE  username = 'APP_USER';
USERNAME    ACCOUNT_STATUS
----------- --------------------------------
APP_USER    LOCKED(TIMED)

💡 Always use DBA_USERS for diagnosis. ALL_USERS exposes fewer columns and will not always give you the full status.

The source of truth: user_astatus_map

Do not memorize the list of values. The database already has it. The user_astatus_map table shows every possible state for your version:

SELECT * FROM user_astatus_map;
   STATUS# STATUS
---------- --------------------------------
         0 OPEN
         1 EXPIRED
         2 EXPIRED(GRACE)
         4 LOCKED(TIMED)
         8 LOCKED
         5 EXPIRED & LOCKED(TIMED)
         6 EXPIRED(GRACE) & LOCKED(TIMED)
         9 EXPIRED & LOCKED
        10 EXPIRED(GRACE) & LOCKED
        32 OPEN & IN ROLLOVER
        33 EXPIRED & IN ROLLOVER
        36 LOCKED(TIMED) & IN ROLLOVER
        37 EXPIRED & LOCKED(TIMED) & IN ROLLOVER
        40 LOCKED & IN ROLLOVER
        41 EXPIRED & LOCKED & IN ROLLOVER

💡 STATUS# is a bitmask. Each state is the sum of base flags. If your version does not support gradual password rollover, you will only see the first 9 values (no & IN ROLLOVER).

What each state means

There are 6 base flags. All 15 values in user_astatus_map are combinations of them added together.

Base flagSTATUS#What happenedAction
OPEN0Account active, password valid.None.
EXPIRED1The password expired (policy or manual expiration).Reset the password.
EXPIRED(GRACE)2The password entered the grace window (PASSWORD_GRACE_TIME). Still connects, but warns.Change it before it turns EXPIRED.
LOCKED(TIMED)4Automatic lock after FAILED_LOGIN_ATTEMPTS wrong tries.Unlock, or wait for PASSWORD_LOCK_TIME to expire.
LOCKED8Manual lock set by the DBA (ACCOUNT LOCK).Unlock manually. It does not clear on its own.
IN ROLLOVER32Password in gradual rollover: old and new are valid at the same time.Finish the migration and end the period.

Reading the combinations is just addition. Examples:

  • 9 = 1 + 8 = EXPIRED & LOCKED
  • 5 = 1 + 4 = EXPIRED & LOCKED(TIMED)
  • 37 = 1 + 4 + 32 = EXPIRED & LOCKED(TIMED) & IN ROLLOVER

⚠️ The distinction that trips people up: LOCKED (8) is a human action, someone ran ACCOUNT LOCK. LOCKED(TIMED) (4) is the database locking the account after N failed logins. If you keep seeing LOCKED(TIMED) come back, it is not maintenance: it is a login with the wrong password (an app holding an old password, a misconfigured job, brute force). Find the source before you just unlock it.

IN ROLLOVER: the newer state that catches people off guard

If the status carries & IN ROLLOVER, the account is in gradual password rollover. This feature arrived in Oracle 21c and was backported to 19c starting in 19.12. It lets you change a user’s password while keeping the old one valid for a period, so applications can migrate with no downtime.

During that period the account shows, for example:

OPEN & IN ROLLOVER

and both the old and the new password work at the same time.

Enable it on the profile (value in days, minimum 1 hour = 1/24, maximum 60 days or the lower of PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME):

ALTER PROFILE app_profile LIMIT PASSWORD_ROLLOVER_TIME 1;

End the rollover early (kills the old password immediately):

ALTER USER app_user EXPIRE PASSWORD ROLLOVER PERIOD;

Turn the feature off on the profile:

ALTER PROFILE app_profile LIMIT PASSWORD_ROLLOVER_TIME 0;

How to fix each case

From the safest action to the most aggressive.

Locked account (LOCKED or LOCKED(TIMED)):

ALTER USER app_user ACCOUNT UNLOCK;

⚠️ If it is LOCKED(TIMED), unlocking without finding the source of the failures only delays the problem: the account locks again on the next round of bad attempts. Check the listener log and the audit trail to see which host is trying with an old password.

Expired password (EXPIRED or EXPIRED(GRACE)):

⚠️ Changing an application user’s password drops connections still using the old one. On 19.12+ and 21c, prefer gradual password rollover to change it without downtime.

ALTER USER app_user IDENTIFIED BY new_password;

Locked and expired at the same time (e.g. EXPIRED & LOCKED):

ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user IDENTIFIED BY new_password;

Reproducing each state in a lab

Want to trigger each state and watch account_status change? Create three dedicated profiles and a test user. Each scenario just points to the right profile, with no back-and-forth ALTER PROFILE. Run everything inside a PDB.

Setup

-- Dedicated profiles (create once)
CREATE PROFILE lab_base LIMIT
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME    1/1440;   -- 1 min: guarantees LOCKED(TIMED) and a fast auto-unlock

CREATE PROFILE lab_grace LIMIT
  PASSWORD_LIFE_TIME  1/1440      -- 1 min
  PASSWORD_GRACE_TIME 1/1440;     -- 1 min

CREATE PROFILE lab_rollover LIMIT
  PASSWORD_LIFE_TIME     UNLIMITED
  PASSWORD_GRACE_TIME    UNLIMITED
  PASSWORD_ROLLOVER_TIME 1/24;    -- 1 hour (minimum allowed)

-- Test user, starts on the base profile
CREATE USER lab_user IDENTIFIED BY "Senha#123" PROFILE lab_base;
GRANT CREATE SESSION TO lab_user;

SELECT account_status FROM dba_users WHERE username = 'LAB_USER';
-- OPEN

⚠️ You cannot pack everything into a single profile. PASSWORD_ROLLOVER_TIME must be at least 1 hour and at most the lower of PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME. Since the grace scenario uses 1 minute, a short grace and rollover are mutually exclusive in the same profile. Combining them returns ORA-65211.

⚠️ If PASSWORD_LOCK_TIME is UNLIMITED, a lock from failed logins shows up as LOCKED (no TIMED), because the account never unlocks on its own. That is why lab_base sets a finite value.

LOCKED (manual lock)

ALTER USER lab_user PROFILE lab_base;

ALTER USER lab_user ACCOUNT LOCK;
SELECT account_status FROM dba_users WHERE username = 'LAB_USER';
-- LOCKED

ALTER USER lab_user ACCOUNT UNLOCK;
-- back to OPEN

LOCKED(TIMED) (automatic lock)

This scenario depends on FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME, so make sure lab_base is active first:

ALTER USER lab_user PROFILE lab_base;

-- get the password wrong 3x (FAILED_LOGIN_ATTEMPTS), each one returns ORA-01017
CONNECT lab_user/wrong_password

SELECT account_status FROM dba_users WHERE username = 'LAB_USER';
-- LOCKED(TIMED)

The timed auto-unlock is lazy: Oracle only re-evaluates the lock on the next login, not through a background timer. That is why DBA_USERS keeps showing LOCKED(TIMED) even after PASSWORD_LOCK_TIME has passed, until someone attempts to connect.

-- option 1: manual unlock, immediate
ALTER USER lab_user ACCOUNT UNLOCK;

-- option 2: once PASSWORD_LOCK_TIME (1 min in this lab) has passed, connect with the correct password.
-- the login triggers the re-evaluation, connects, and the status goes back to OPEN
CONNECT lab_user/"Senha#123"

⚠️ Querying DBA_USERS does not unlock anything. Only a login attempt re-evaluates PASSWORD_LOCK_TIME. Waiting and running SELECT again will keep showing LOCKED(TIMED).

EXPIRED (manual expiration)

ALTER USER lab_user PROFILE lab_base;

ALTER USER lab_user PASSWORD EXPIRE;
SELECT account_status FROM dba_users WHERE username = 'LAB_USER';
-- EXPIRED

ALTER USER lab_user IDENTIFIED BY "Senha#123";
-- back to OPEN

EXPIRED(GRACE) (time-based grace)

This one depends on time passing. Switch to lab_grace, reset the password clock, wait, then connect:

ALTER USER lab_user PROFILE lab_grace;
ALTER USER lab_user IDENTIFIED BY "Senha#123";   -- resets the password clock

-- wait ~1 minute, then connect (the connection triggers the check)
CONNECT lab_user/"Senha#123"
-- Warning ORA-28002: the password will expire within ... days

SELECT account_status FROM dba_users WHERE username = 'LAB_USER';
-- EXPIRED(GRACE)

Once PASSWORD_GRACE_TIME passes, the status turns to EXPIRED. To clean up, move back to the base profile and reset the password:

ALTER USER lab_user PROFILE lab_base;
ALTER USER lab_user IDENTIFIED BY "Senha#123";

OPEN & IN ROLLOVER (gradual rollover)

Needs 19.12+ or 21c. Switch to lab_rollover and change the password:

ALTER USER lab_user PROFILE lab_rollover;

ALTER USER lab_user IDENTIFIED BY "NovaSenha#123";
SELECT account_status FROM dba_users WHERE username = 'LAB_USER';
-- OPEN & IN ROLLOVER

During the rollover, both passwords connect. End it early (only the new one stays valid):

ALTER USER lab_user EXPIRE PASSWORD ROLLOVER PERIOD;
-- back to OPEN

Combinations

Just stack the triggers. Example of EXPIRED & LOCKED (on lab_base):

ALTER USER lab_user PROFILE lab_base;
ALTER USER lab_user PASSWORD EXPIRE;
ALTER USER lab_user ACCOUNT LOCK;
SELECT account_status FROM dba_users WHERE username = 'LAB_USER';
-- EXPIRED & LOCKED

Cleanup

DROP USER lab_user CASCADE;
DROP PROFILE lab_base;
DROP PROFILE lab_grace;
DROP PROFILE lab_rollover;

Quick checklist

-- 1. Check the current status
SELECT username, account_status FROM dba_users WHERE username = 'APP_USER';

-- 2. List the possible values for your version
SELECT * FROM user_astatus_map;

-- 3. Unlock
ALTER USER app_user ACCOUNT UNLOCK;

-- 4. Reset the password (watch out for downtime)
ALTER USER app_user IDENTIFIED BY new_password;

References

Share.

Related Posts

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted