ORA-01017: invalid username/password; logon denied

When encountering an ORA-01017 error, it means that the login is denied. The main reason is that the user name or password is wrong or the account status is abnormal. 1. First, manually check the user name and password, and note that the upper and lower case and special characters must be wrapped in double quotes; 2. Confirm that the service name or SID of the connection is correct, and you can test the connection through tnsping; 3. Check whether the account is locked or the password expires, and the DBA needs to query the dba_users view to confirm the status; 4. If the account is locked or expired, you need to execute the ALTER USER command to unlock and reset the password; 5. Note that Oracle 11g and above versions are case-sensitive by default, and you need to ensure that the input is accurate. 6. When logging in to special users such as SYS, you should use the as sysdba method, and ensure that the password file and listening configuration are correct; 7. Check whether the password file exists and the REMOTE_LOGIN_PASSWORDFILE parameter settings in advanced scenarios. The complete investigation needs to be verified one by one according to the steps to ultimately ensure that the authentication information is consistent with the database configuration, and the problem can be solved.

When you encounter an ORA-01017: invalid username/password; logon denied error, it means that the Oracle database rejects your login request, usually because the username or password is incorrect. Although the problem may seem simple, it may be caused by a variety of reasons in actual use. Here are the common causes and solutions:

✅ 1. Check whether the username and password are correct

This is the most common reason, especially when:

• Passwords are case sensitive (especially in 11g and above)
• Incorrect characters were used (such as 0 and O , l and 1 )
• Copy and paste with spaces

suggestion:

• Manually re-enter the username and password
• Make sure Caps Lock is not turned on
• If using tools (such as SQL*Plus, Toad, PL/SQL Developer), try to explicitly type in the command line:

 sqlplus username/password@service_name

⚠️ Note: If the password contains special characters (such as @ , / , # ), it needs to be enclosed in double quotes:

 sqlplus myuser/"P@ssw0rd123"@orcl

✅ 2. Confirm whether the database service name or SID is correct

Even if the username and password are correct, connecting to the wrong instance can cause login to fail or timeout, and sometimes it can appear as ORA-01017.

Inspection method:

• Confirm that the service name in the TNS configuration ( tnsnames.ora ) is correct
• Test connection using tnsping :

 tnsping orcl

• Or use the full connection string directly in SQL*Plus:

 sqlplus username/password@//hostname:port/service_name

For example:

 sqlplus scott/tiger@//192.168.1.100:1521/orclpdb

✅ 3. Check whether the account is locked or expired

Oracle users may be locked due to multiple login failures, or their password has expired.

How to confirm: Log in to the database as DBA and execute:

 SELECT username, account_status, expiry_date, lock_date 
FROM dba_users 
WHERE username = 'YOUR_USERNAME';

Common Status:

• LOCKED : Account is locked
• EXPIRED : Password expired
• EXPIRED(GRACE) : Expired but within grace period

Solution (requires DBA permission):

 -- Unlock the account ALTER USER scott ACCOUNT UNLOCK;

-- Reset password and unlock ALTER USER scott IDENTIFIED BY newpassword ACCOUNT UNLOCK;

✅ 4. Pay attention to password case sensitive settings

Starting with Oracle 11g, password case sensitivity is enabled by default ( SEC_CASE_SENSITIVE_LOGON=TRUE ).

This means:

• Password123 ≠ password123

Inspection method:

 SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

suggestion:

• Strictly match case when entering a password
• If you need to temporarily shut down (production environment is not recommended):

   ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;

✅ 5. Check whether the correct authentication method is used

Some users (such as SYS ) must log in using a specific method:

 sqlplus sys/password as sysdba

If you log in to SYS in normal way, it may fail.

Notice:

• AS SYSDBA requires operating system authentication or password file support
• Make sure ORACLE_SID is set correctly
• On Windows, you may need to run the command line as a service account

✅ 6. Password file or listener issues (less common)

If you use SYSDBA to log in to the remote database, but the password file is corrupt or not configured correctly, you may also report ORA-01017.

Check items:

• Whether the password file exists ( orapw<sid></sid> )
• Is the parameter REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE
• Is there multiple instances confusion

Summary of the steps to check

• [ ] 1. Manually enter the username/password, pay attention to case and special characters
• [ ] 2. Use tnsping to test the connection service name
• [ ] 3. Check the account status (locked/expired)
• [ ] 4. DBA assists in unlocking or resetting passwords
• [ ] 5. Confirm whether AS SYSDBA permission is required
• [ ] 6. Check password file and listening configuration (advanced scenario)

Basically these common reasons. ORA-01017 seems simple, but it may become complicated after being configured in combination with the environment. The key is to first confirm "whether you really lost the right one", and then gradually eliminate account status and connection issues.

The above is the detailed content of ORA-01017: invalid username/password; logon denied. For more information, please follow other related articles on the PHP Chinese website!