When an Oracle account works after an administrator unlocks it and then becomes locked again, the usual cause is not the unlock command. A service, scheduled task, connection pool, script, monitoring agent, or forgotten workstation is still presenting an old password.
Do not solve this by repeatedly unlocking the user or disabling the failed-login limit. Use the audit trail to identify the source, update the stored credential there, and only then restore access.
The examples use APPUSER as a placeholder. Run diagnostic queries with an appropriately privileged account and substitute the real database username without publishing it in tickets or articles.
Confirm the Account State and Profile
SELECT username,
account_status,
lock_date,
profile,
authentication_type
FROM dba_users
WHERE username = UPPER('APPUSER');
Oracle’s current ORA-28000 documentation lists three broad causes: too many consecutive incorrect credentials, an administrator lock, or a common user locked in the root container. LOCKED(TIMED) commonly points to the profile’s failed-login policy, while LOCKED requires checking whether the lock was manual or indefinite.
Inspect the relevant limits rather than assuming the DEFAULT profile:
SELECT profile,
resource_name,
limit
FROM dba_profiles
WHERE profile = (
SELECT profile
FROM dba_users
WHERE username = UPPER('APPUSER')
)
AND resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME')
ORDER BY resource_name;
FAILED_LOGIN_ATTEMPTS controls how many consecutive authentication failures trigger a lock. PASSWORD_LOCK_TIME controls the timed lock duration. Raising either value can hide the symptom and weaken protection; it does not repair the stale credential.
Query Failed Logons in Unified Auditing
First check whether unified auditing is enabled:
SELECT parameter, value
FROM v$option
WHERE parameter = 'Unified Auditing';
If it is enabled, query recent failed logons:
SELECT event_timestamp,
dbusername,
os_username,
userhost,
client_program_name,
authentication_type,
return_code
FROM unified_audit_trail
WHERE action_name = 'LOGON'
AND dbusername = UPPER('APPUSER')
AND return_code <> 0
ORDER BY event_timestamp DESC
FETCH FIRST 100 ROWS ONLY;
Oracle documents UNIFIED_AUDIT_TRAIL as the unified view for audit records. In this result:
USERHOSThelps identify the originating computer;OS_USERNAMEmay identify the operating-system context;CLIENT_PROGRAM_NAMEcan expose the driver or application;RETURN_CODE = 1017represents invalid credentials;RETURN_CODE = 28000means the account was already locked when that attempt arrived.
The first burst of 1017 records is usually more useful than hundreds of later 28000 records. Group the events to reveal the pattern:
SELECT userhost,
client_program_name,
return_code,
COUNT(*) AS attempts,
MIN(event_timestamp) AS first_seen,
MAX(event_timestamp) AS last_seen
FROM unified_audit_trail
WHERE action_name = 'LOGON'
AND dbusername = UPPER('APPUSER')
AND return_code <> 0
AND event_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
GROUP BY userhost, client_program_name, return_code
ORDER BY last_seen DESC;
If the view contains no relevant records, verify which audit policies are enabled:
SELECT policy_name, enabled_option, entity_name, success, failure
FROM audit_unified_enabled_policies
ORDER BY policy_name, entity_name;
The predefined ORA_LOGON_FAILURES policy can capture failed logons. Enable it only after confirming that it is not already active and that the change follows the site’s audit-retention plan:
AUDIT POLICY ORA_LOGON_FAILURES;
Audit cannot reconstruct failed logons that were never recorded. After enabling the required policy, reproduce one controlled failed connection or wait for the recurring source, then query again. Oracle’s audit documentation should be matched to the database release and its unified or mixed audit mode.
Check the Traditional Audit Trail When Appropriate
On systems using traditional auditing rather than pure unified auditing, inspect DBA_AUDIT_TRAIL:
SELECT timestamp,
username,
os_username,
userhost,
terminal,
returncode,
action_name
FROM dba_audit_trail
WHERE action_name = 'LOGON'
AND username = UPPER('APPUSER')
AND returncode <> 0
ORDER BY timestamp DESC;
Do not assume that querying the wrong view and receiving zero rows means no failed connections occurred. Audit mode, policy state, container, privileges, and retention all affect what is visible.
Trace the Credential on the Source Host
Once USERHOST and CLIENT_PROGRAM_NAME identify a likely system, search that host’s authorised configuration stores. Typical locations include:
- Windows services and their application configuration;
- Task Scheduler jobs;
- IIS application pools and deployed
.configfiles; - Java application servers, JDBC pools, wallets, and secrets stores;
- ODBC data sources;
- monitoring and backup agents;
- CI/CD variables and automation vaults;
- Linux systemd units, cron jobs, and application environment files.
Search for the database service name and username, not for the password text. Credentials may be encrypted, stored in a wallet, injected at runtime, or managed by a service account system. Never paste live connection strings into a ticket or a broad recursive-search output.
Connection pooling explains why the lock can appear delayed. An application may continue serving established sessions and only present the old password when it opens a new connection or recycles the pool.
Unlock Only After Fixing the Source
Update or rotate the credential in the responsible application, restart or recycle the relevant pool under change control, and verify a successful test connection. Then unlock the account:
ALTER USER APPUSER ACCOUNT UNLOCK;
Watch the failed-logon query immediately afterward. A clean result followed by normal application operation confirms more than an unlock alone. If failures continue, use their timestamps and hosts to find a second stale consumer.
Changing the database profile to tolerate more failures should be a documented, temporary exception only. The correct durable fix is to remove every obsolete credential, preserve a useful audit trail, and make credential rotation an application-owned process rather than an emergency DBA task.
