ORA-28000 and ORA-28001: Fix a Locked or Expired Oracle Account Without an Outage

ORA-28000 means the Oracle account is locked, either after too many failed logins in a row or because someone locked it. ORA-28001 means the account's password has expired. For a person, one ALTER USER statement fixes either; for an application account, first find what is still sending the wrong password, or the next run of failed logins locks it again.

Both errors can stop an application while the database itself is perfectly healthy. Nothing inside Oracle is broken: a profile limit did exactly what it was set to do.

This guide covers what each error means, how to read the account status, how to find the client that keeps failing, and how to change a service account password with no outage. Every limit, status and command below is checked against Oracle's 19c documentation, with 26ai differences noted where they exist.

1. What Do ORA-28000 and ORA-28001 Mean?

ORA-28000 is Oracle's "account is locked" error: the login is refused because an administrator locked the account or because the wrong password was used too many times in a row. ORA-28001 is the "password has expired" error: the password outlived its PASSWORD_LIFE_TIME and grace period, or an administrator expired it, and it must be changed before the login can continue.

Oracle reworded these messages in 26ai, so the text in your log depends on the release. The table gives both versions, from Oracle's error reference.

ORA-28002 deserves more attention than it gets. It appears on every login during the grace period, yet Oracle itself notes that some applications "may not be equipped to handle error and warning messages when a user logs in", so the first visible sign can be the ORA-28001 when the grace period ends (7 days in the DEFAULT profile).

An ORA-12154 or ORA-12514 is a different problem: those are name-resolution and listener errors, which my guide to listener and TNS troubleshooting for ORA-12154 and ORA-12514 covers.

2. Which Profile Limits Lock or Expire an Account?

Every user has a profile, and a user without an explicit one gets the DEFAULT profile. Password limits are always on: Oracle's CREATE PROFILE reference says the RESOURCE_LIMIT parameter "does not apply to password resources. Password resources are always enabled."

These are the password settings of the DEFAULT profile, as Oracle's 19c Security Guide lists them:

Time limits are in days, and fractions are allowed: Oracle's own examples use 1/24 for one hour. That makes it easy to test a profile on a non-production database without waiting a week.

Check the real values, not the documented ones

Defaults can be changed, and not every database starts with them. Oracle notes that databases created with DBCA "will have these settings, but manually created databases do not" unless someone runs secconf.sql.

26ai gives one more reason to check. Its Security Guide table lists INACTIVE_ACCOUNT_TIME as 365 in the DEFAULT profile, while a later section of the same page and the 26ai SQL Language Reference still give UNLIMITED as the default.

Your own data dictionary settles it:

-- the DEFAULT profile's password limits (SQL, any OS)
SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'DEFAULT'
AND    resource_type = 'PASSWORD'
ORDER  BY resource_name;

-- the password limits of the profile one account uses
SELECT p.resource_name, p.limit
FROM   dba_users u
JOIN   dba_profiles p ON p.profile = u.profile
WHERE  u.username = 'APP_USER'
AND    p.resource_type = 'PASSWORD'
ORDER  BY p.resource_name;

A LIMIT of DEFAULT in the second result means that value comes from the DEFAULT profile, so read it from the first result.

Multitenant: the root's profile decides for common users

In a CDB, the password settings of a common user come from the profile assigned to that user in the root, even when the user logs in to a PDB. Oracle's example: a common profile with FAILED_LOGIN_ATTEMPTS 1 in the root beats a local PDB profile set to 6.

So when a common user locks "too early" inside a PDB, read the root's profile first. My guide to managing CDBs and PDBs in production covers common and local users in more depth.

3. How Do You Read the Account Status?

Start with one query. It tells you which problem you really have and when it began.

-- run as a DBA (SQL, any OS)
SELECT username, account_status, lock_date, expiry_date,
       profile, last_login, password_change_date
FROM   dba_users
WHERE  username = 'APP_USER';

PASSWORD_CHANGE_DATE is documented in the 19c Reference. On a release where that column is missing, Oracle's Security Guide reads the same date from the PTIME column of SYS.USER$.

Two quirks catch people out, and both are about changes the clock makes. When a password lifetime runs out, the status moves on only when the account next logs in with the correct password, because, in Oracle's words, "Oracle Database does not have any background process to update the account status."

And after a timed lock has passed, DBA_USERS can still say locked; Oracle notes that "after the user connects, the information in DBA_USERS is updated with the correct OPEN status."

An administrator's own change does not wait for a login. ALTER USER ... ACCOUNT LOCK is there to "disable access" and ACCOUNT UNLOCK to "enable access to the account", PASSWORD EXPIRE forces a password change before the user can log in again, and after IDENTIFIED BY, every later connection must give the new password, unless a gradual rollover period is running (section 9).

LAST_LOGIN is not filled in for administrative connections such as AS SYSDBA, so an empty or old value there does not prove nobody used the account.

4. How Do You Fix ORA-28000 Without It Coming Back?

The unlock itself is one line. The order of the steps around it is what keeps the application up.

1. Read the status first. LOCKED(TIMED) means failed logins. A plain LOCKED may mean a person locked the account on purpose, so ask before you open it.
2. Find and stop the source of the failed logins, using the audit queries in section 5.
3. Unlock the account with ALTER USER ... ACCOUNT UNLOCK. It needs the ALTER USER system privilege.
4. If it is also expired, unlock it and set the new password in one statement, in the same form as Oracle's own example.
5. Watch the failed-logon records for a while afterwards, in case a second client is still using the old password.

-- unlock only (SQL, any OS)
ALTER USER app_user ACCOUNT UNLOCK;

-- locked AND expired: one statement, the form Oracle's Security Guide uses
ALTER USER app_user ACCOUNT UNLOCK IDENTIFIED BY <new_password>;

Waiting is also an option for LOCKED(TIMED): the account opens by itself after PASSWORD_LOCK_TIME, one day in the DEFAULT profile. For an application that is down, a day is rarely acceptable, which is why finding the source matters more than the unlock.

Common users in a CDB

A common user locked in the CDB root is locked in every PDB of that CDB. Oracle's Security Guide adds that such an account cannot be unlocked in a PDB, so connect to the root and unlock it there.

FAILED_LOGIN_ATTEMPTS is not SEC_MAX_FAILED_LOGIN_ATTEMPTS

The profile limit locks the account. The SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter, 3 by default, only drops one connection after that many failed tries on it, and Oracle points out that the profile limit applies only to valid user accounts.

Oracle also slows a failing client down: "Each time the user unsuccessfully logs in, Oracle Database increases the delay exponentially with each login failure." So a client stuck on a bad password gets slower with every try.

5. How Do You Find What Keeps Locking the Account?

An account locked by failed logins means something sent a wrong password several times in a row. On a newly created database Oracle is already recording it: the ORA_LOGON_FAILURES unified audit policy "is enabled by default", but Oracle adds that it "is not enabled for databases that were upgraded from earlier versions".

So check first, and enable it if it is missing:

-- is the failed-logon policy enabled? (SQL, any OS)
SELECT policy_name, enabled_option, entity_name, success, failure
FROM   audit_unified_enabled_policies
WHERE  policy_name = 'ORA_LOGON_FAILURES';

-- no row returned? Oracle's statement to enable it for failed logons
-- (needs the AUDIT SYSTEM privilege or the AUDIT_ADMIN role)
AUDIT POLICY ORA_LOGON_FAILURES WHENEVER NOT SUCCESSFUL;

Enabling it adds an audit record for each failed logon, so the unified audit trail grows and belongs in your purge routine. My Oracle database security and hardening guide covers audit trail housekeeping with DBMS_AUDIT_MGMT.

Then ask the trail who failed. Reading it needs the AUDIT_VIEWER or AUDIT_MGMT role.

-- who sent a wrong password for APP_USER in the last day?
SELECT userhost, os_username, client_program_name,
       COUNT(*) AS failures, MAX(event_timestamp) AS last_seen
FROM   unified_audit_trail
WHERE  action_name = 'LOGON'
AND    return_code = 1017
AND    dbusername  = 'APP_USER'
AND    event_timestamp > SYSDATE - 1
GROUP  BY userhost, os_username, client_program_name
ORDER  BY failures DESC;

RETURN_CODE holds the Oracle error code of the action, so 1017 is the wrong-password case. USERHOST is the host the session came from and CLIENT_PROGRAM_NAME is the program, which together name the server and the process to fix.

No rows does not settle the question. The query looks back only one day, so older failures are outside it, and records your audit-trail cleanup has already purged are gone. The policy may also not have covered this user at the time (it can be enabled for, or except, named users), or you may be connected to a different container, root or PDB, from the one the client logs in to.

So widen the time window, check your audit retention and the container you are in, and look at the other ways an account gets locked: an administrator's ACCOUNT LOCK (the next query) and INACTIVE_ACCOUNT_TIME (section 2).

Who locked it on purpose?

A plain LOCKED status may be a person, not a client. On new databases the ORA_SECURECONFIG policy, also enabled by default, audits ALTER USER and ALTER PROFILE, so the same trail can answer that question too.

-- recent audited actions on the APP_USER account itself
SELECT event_timestamp, dbusername, userhost, action_name
FROM   unified_audit_trail
WHERE  object_name = 'APP_USER'
AND    event_timestamp > SYSDATE - 7
ORDER  BY event_timestamp;

OBJECT_NAME is the object the action affected; for an ALTER USER, that is the user being changed.

Where a stale password hides

A database user's password can be stored in more places than anyone remembers. Check each of these before you unlock or change anything:

• Application server connection pools, their configuration files and any wallet they read.
• Oracle REST Data Services pools, which each keep their own stored copy of the password.
• Fixed-user database links on other databases. CONNECT TO user IDENTIFIED BY password stores the password in the link, and Oracle's reference points to ALTER DATABASE LINK for when it changes.
• Scripts, scheduled jobs and monitoring agents on other servers that log in as the account.
• An Active Data Guard standby, which by default counts login failures locally. ADG_ACCOUNT_INFO_TRACKING = GLOBAL makes failures across the whole Data Guard configuration count towards one limit.

I met exactly this in May 2026, on an Oracle REST Data Services setup where two connection pools logged in to one database as the same user. A reconfiguration reset that user's password and refreshed one pool's stored copy; the other pool still held the old one and failed with ORA-01017, which ORDS showed as HTTP status 574.

Updating the second pool's stored password and restarting ORDS brought it back. The lesson I kept: a password belongs to the database user, not to one client, so changing it means finding every copy first.

6. How Do You Fix ORA-28001 When the Password Has Expired?

There is no "unexpire" switch. Oracle's Security Guide says it plainly: there is no "password unexpire" clause, "but an account can be 'unexpired' by changing the password on the account."

-- an administrator sets a new password (SQL, any OS)
ALTER USER app_user IDENTIFIED BY <new_password>;

-- or the user changes their own password with the SQL*Plus command
PASSWORD

Can you set the same password again? Only if the profile's reuse rules allow it. In the DEFAULT profile both reuse limits are UNLIMITED, which Oracle says means both are ignored and any password can be reused; a profile that sets them refuses a repeat with ORA-28007, "the password cannot be reused".

Even where reuse works, I would treat it as a stopgap. The expiry was the reminder to rotate, and the same password simply restarts the same clock.

Why an application cannot fix this by itself

When a person logs in with an expired password, SQL*Plus asks for a new one, as Oracle's own example shows. The ALTER USER reference says the same: "tools such as SQL*Plus allow the user to change the password on the first attempted login following the expiration."

An application's connection pool has nobody sitting at that prompt. Each new connection fails until a DBA changes the password and every client receives the new one, and that gap is the outage.

Does setting PASSWORD_LIFE_TIME to UNLIMITED fix it?

Not for an account that is already in trouble. Oracle notes that UNLIMITED "only affects accounts that have not entered their grace period. After the grace period expires, the user must change the password."

Expired on a standby

On a read-only Active Data Guard standby, an expired password returns ORA-28032 instead, because the change cannot be written there. Oracle's action is to change the password on the primary and then retry on the standby; my Oracle 19c Data Guard guide covers the standby side.

7. Why Can Lowering PASSWORD_LIFE_TIME Expire Accounts at Once?

This one surprises people. PASSWORD_LIFE_TIME is measured from each account's last password change, or from its creation if the password was never changed, and not from the moment you edited the profile.

So if you cut a profile from 180 days to 90, for example, every account on it whose password is older than 90 days is already past its lifetime. Oracle says such accounts "immediately expire and enter their grace period on their next connection", with the ORA-28002 warning.

Before you tighten a profile, list who it will catch:

-- accounts on APP_PROF whose password is older than 90 days
SELECT username, account_status, password_change_date
FROM   dba_users
WHERE  profile = 'APP_PROF'
AND    password_change_date < SYSDATE - 90
ORDER  BY password_change_date;

The same page has one more catch. A user who is already logged in when you change the limit can stay logged in beyond the new expiry date, because the status is worked out at login, not during a session.

8. Should Application Service Accounts Ever Expire?

Oracle's advice on the DEFAULT profile is, "For greater security, use the default settings that are described in that table, based on your needs." My own rule for application service accounts is narrower: give them a profile of their own, so their limits are a decision rather than an accident of DEFAULT.

What goes into that profile is a call for you and your security team, and the trade-offs are real. A long or unlimited lifetime avoids surprise expiries but can leave one password in use for years; a lockout limit stops password guessing but also lets one misconfigured client take the application down.

Whatever you choose, pair it with two habits: a rotation date you own, and a check that warns you before an expiry or a lock does.

-- non-Oracle accounts expiring in the next 14 days (14 is an example window)
SELECT username, account_status, expiry_date, profile
FROM   dba_users
WHERE  oracle_maintained = 'N'
AND    expiry_date < SYSDATE + 14
ORDER  BY expiry_date;

-- non-Oracle accounts locked in the last day
SELECT username, account_status, lock_date
FROM   dba_users
WHERE  oracle_maintained = 'N'
AND    account_status LIKE '%LOCKED%'
AND    lock_date > SYSDATE - 1;

Choose a warning window longer than the time your change process needs to approve and deploy a new password. Rotation itself no longer needs a maintenance window, which is the next section.

9. How Does Gradual Password Rollover Avoid Downtime?

Gradual database password rollover lets an account accept both its old and its new password for a period you set. Oracle added it in Oracle Database 19c release 19.12, so that "an application can change its database passwords without an administrator having to schedule downtime."

It is built for application service accounts. Administrative users are excluded, and Oracle says "You cannot grant administrative privileges to users who have a password rollover-enabled profile."

1. Create a profile with a non-zero PASSWORD_ROLLOVER_TIME, check in DBA_PROFILES what it takes from DEFAULT, then give it to the account.
2. Change the password. The rollover period starts the moment the password changes.
3. Confirm the status shows IN ROLLOVER.
4. Update every client before the period ends.
5. Find the stragglers still logging in with the old password.
6. End the rollover early once every client has moved, or let it expire.

-- 1. a profile for service accounts (1 day is an example value)
CREATE PROFILE app_svc_prof LIMIT
  PASSWORD_ROLLOVER_TIME 1;

-- 1a. before moving the account: the new profile next to DEFAULT
--     (a LIMIT of DEFAULT on an APP_SVC_PROF row = the DEFAULT row's value applies)
SELECT resource_name, profile, limit
FROM   dba_profiles
WHERE  profile IN ('DEFAULT', 'APP_SVC_PROF')
AND    resource_type = 'PASSWORD'
ORDER  BY resource_name, profile;

-- 1b. move the account once those values are the ones you want
ALTER USER app_user PROFILE app_svc_prof;

-- 2. the password change starts the rollover period
ALTER USER app_user IDENTIFIED BY <new_password>;

-- 3. expect OPEN & IN ROLLOVER
SELECT username, account_status
FROM   dba_users
WHERE  username = 'APP_USER';

Limits not named in the new profile come from the DEFAULT profile as it stands on your database at the time. That may not be the documented 180-day lifetime: DEFAULT can have been changed, and manually created databases may never have had those values (section 2).

Step 1a shows both profiles side by side. If any lifetime, lockout or grace value is not what the service account should have, set it in the new profile before step 1b moves the account.

The period has bounds: at least one hour, and at most 60 days or the profile's PASSWORD_LIFE_TIME or PASSWORD_GRACE_TIME, whichever is lowest. With the documented DEFAULT values, Oracle's own table puts the ceiling at 7 days; with a customised DEFAULT, the ceiling follows your values.

Finding the clients that have not moved

Oracle's documented query looks for successful logons made with the old password's verifier. It reads successful LOGON records, and ORA_LOGON_FAILURES records failures only, so audit this account's successful logons first.

-- audit successful logons by this account (same form as Oracle's policy definitions)
CREATE AUDIT POLICY app_user_logons ACTIONS LOGON;
AUDIT POLICY app_user_logons BY app_user WHENEVER SUCCESSFUL;

-- 5. who still logs in with the OLD password? (Oracle's query, as documented)
SELECT DBUSERNAME, AUTHENTICATION_TYPE, OS_USERNAME, USERHOST, EVENT_TIMESTAMP
FROM   UNIFIED_AUDIT_TRAIL
WHERE  ACTION_NAME='LOGON' AND EVENT_TIMESTAMP > SYSDATE-1
AND    REGEXP_LIKE(AUTHENTICATION_TYPE, '(VERIFIER=.*?-OLD)');

-- 6. every client has moved: end the rollover now
ALTER USER app_user EXPIRE PASSWORD ROLLOVER PERIOD;

An AUDIT statement takes effect in sessions that start after it, so connections a pool opened earlier show up only when they reconnect. When the rollover is done, disable the policy with NOAUDIT POLICY app_user_logons BY app_user; Oracle's reference warns that, for a policy enabled for named users, a NOAUDIT without the BY clause "will have no effect".

Three behaviours to plan for

• After the period ends, the old password gets ORA-1017 and is "recorded as a failed login attempt", so a forgotten client can still lock the account through FAILED_LOGIN_ATTEMPTS.
• Data Pump exports only the new password's verifier for an account in rollover, so after an import only the new password works. My Data Pump expdp and impdp guide covers the rest of a migration.
• Data Guard needs ADG_ACCOUNT_INFO_TRACKING set to GLOBAL for rollover, according to Oracle's Security Guide.

If a password may have been stolen, skip the rollover. Oracle's guideline is to change it and expire the old one in a single statement:

-- suspected compromise: new password, old one rejected at once
ALTER USER app_user IDENTIFIED BY <new_password> EXPIRE PASSWORD ROLLOVER PERIOD;

10. What Is the Runbook When an Application Account Fails?

This is the order I would follow, built from the sections above. It works the same for ORA-28000 and ORA-28001 because the first steps only read.

1. Read the exact error in the application log: ORA-28000, ORA-28001, ORA-28002, ORA-01017 or ORA-28032.
2. Query DBA_USERS for the status, the lock and expiry dates, the profile and the last password change.
3. If it is locked, run the failed-logon query and fix or stop the client sending the wrong password.
4. Decide: unlock, set a new password, or both in one statement.
5. If the password changed, update every stored copy: pools, wallets, ORDS pools, fixed-user database links, scripts and agents.
6. On Data Guard, remember the standby can report its own errors, such as ORA-28032.
7. Re-check the status after the clients reconnect, and keep an eye on the failed-logon records.
8. Afterwards, put the account on its own profile and add the expiry and lock checks to monitoring.

For errors outside this pair, my systematic method for diagnosing Oracle errors applies unchanged: read the exact message, find the layer, then fix.

ORA-28000 and ORA-28001 are not database failures. They are profile limits doing their job, and the outage comes from not knowing which clients hold a copy of the password; know that, and the fix really is one ALTER USER away.

Frequently Asked Questions

How do I unlock an Oracle account that shows ORA-28000?

Run ALTER USER username ACCOUNT UNLOCK as a user with the ALTER USER system privilege. First find and fix whatever sent the wrong password, using the failed-logon records in UNIFIED_AUDIT_TRAIL, or the account locks again after the next run of failed logins. A LOCKED(TIMED) account also opens by itself once PASSWORD_LOCK_TIME passes.

How do I fix ORA-28001, the password has expired?

Set a new password with ALTER USER username IDENTIFIED BY new_password, then update every client that stores it. Oracle has no separate unexpire command: changing the password is what unexpires the account. Setting PASSWORD_LIFE_TIME to UNLIMITED afterwards does not rescue an account whose grace period has already ended.

What is the default PASSWORD_LIFE_TIME in Oracle 19c?

The DEFAULT profile in Oracle Database 19c sets PASSWORD_LIFE_TIME to 180 days and PASSWORD_GRACE_TIME to 7 days, with FAILED_LOGIN_ATTEMPTS 10 and PASSWORD_LOCK_TIME 1 day. Oracle notes that manually created databases do not get these settings unless secconf.sql is run, so query DBA_PROFILES to see your real values.

Why does my account show EXPIRED(GRACE)?

Its PASSWORD_LIFE_TIME has passed and the grace period is running. Logins still work but return the ORA-28002 warning, and once the grace period ends the next login fails with ORA-28001. Change the password before then.

Can I change an application's database password without downtime?

Yes, from Oracle Database 19c release 19.12 onward, with gradual password rollover. Give the account a profile with a non-zero PASSWORD_ROLLOVER_TIME and change the password; the old and new passwords both work until the period ends or you run ALTER USER username EXPIRE PASSWORD ROLLOVER PERIOD.

How do I find which machine is locking an Oracle account?

Query UNIFIED_AUDIT_TRAIL for LOGON records with RETURN_CODE 1017 for that user, grouped by USERHOST, OS_USERNAME and CLIENT_PROGRAM_NAME. The ORA_LOGON_FAILURES policy that writes those records is enabled by default only in newly created databases, so on an upgraded database check AUDIT_UNIFIED_ENABLED_POLICIES first.

Can I unlock a common user from inside a PDB?

No. Oracle's Security Guide says a common user locked in the CDB root cannot log in to any PDB of that CDB and cannot be unlocked in a PDB. Connect to the root and unlock it there.