ora 28001 the password has expired что делать
How to Resolve ORA-28001: the password has expired
ORA-28001
ORA-28001 means that your password has been expired according to your profile. Normally, you should change the password, but you can still work around it. Don’t be confused with ORA-28000: The account is locked, it’s a different account problem.
SQL> conn hr/hr
ERROR:
ORA-28001: the password has expired
Solutions to ORA-28001
There’re two methods to solve ORA-28001, the first one is to change password as they want. The other is to set the original password for users.
Method 1: Change Password
Of course, user can change the password immediately at connect-time.
Changing password for hr
New password:
Retype new password:
Password changed
Connected.
A privileged user can do it too.
SQL> conn / as sysdba
Connected.
SQL> alter user hr identified by hr;
I know what you’re looking for, just keep reading the post.
Method 2: Set Original Password
In some cases, changing password can not be done because the password is bound to specific application and even worse, no one remember the original one. So is there any way to unexpire the password? No, no syntax can achieve that, we have to set the original password for our user.
Now, let’s see what we can do to find the original password.
1. Generate the user’s DDL.
SQL> conn / as sysdba
Connected.
SQL> set long 4000;
SQL> select dbms_metadata.get_ddl(‘USER’,’HR’) stmt from dual;
CREATE USER «HR» IDENTIFIED BY VALUES ‘S:E129CFF697C9B08E613D3B22B13DC1124B80
F9346C79FAECF8426B7F3580;T:3716DFD59DAF47A9DA01E6EAE75FC3A86D4911C6FD494F5517FE1
4E19F89E781BF193E27338CE1D8F3BEE184B1B22DD1B5C3A6A355E7EB7118E53C30B5CE60E246916
2F618D658D761207BDF81523871′
DEFAULT TABLESPACE «EXAMPLE»
TEMPORARY TABLESPACE «TEMP»
PASSWORD EXPIRE
2. Set Password by VALUES String
In the output, the string follows IDENTIFIED BY VALUES is the original encrypted password. We can use it to set the password. Just don’t forget to remove line breaks in betweens before using it.
SQL> alter USER «HR» IDENTIFIED BY VALUES ‘S:E129CFF697C9B08E613D3B22B13DC1124B80F9346C79FAECF8426B7F3580;T:3716DFD59DAF47A9DA01E6EAE75FC3A86D4911C6FD494F5517FE14E19F89E781BF193E27338CE1D8F3BEE184B1B22DD1B5C3A6A355E7EB7118E53C30B5CE60E2469162F618D658D761207BDF81523871’;
Now the user’s password is unexpired, just like nothing happened.
SQL> conn hr/hr;
Connected.
Please note that, changing the profile with unlimited PASSWORD_LIFE_TIME for the user can not save ORA-28001 at this moment. But it makes your password unexpired afterwards.
Oracle ORA-28001: the password has expired
Перестало работать приложение, использующее СУБД Oracle XE, в логе сообщение об истечении срока действия пароля: ORA-28001: the password has expired. При чем, было неизвестно под каким пользователем коннектится приложение. Но ясно, что искать надо просроченные пароли.
Приконнектиться к базе как супер админ:
Посмотреть пользователей с истекшими паролями:
Стало ясно, что искомый пользователь ORAUSER. Теперь надо его разлочить и сменить\переназначить пароль:
Проверить статус и дополнительно посмотреть дату истечения пароля:
Видно, что учетка работает, но 3 января срок действия пароля снова истечет. Чтобы этого не происходило надо отключить данную возможность. Для этого надо узнать к какому профилю относится пользователь и отключить истечение времени действия пароля для этого профиля:
Убедиться что все сработало. Поле EXPIRY_DATE должно быть пустым:
Похожие записи
Комментарии к посту “ Oracle ORA-28001: the password has expired ”
Хорошо, что так примитивно рассказано. Ораклисты скажут, что нужно вспоминать матчасть. А у меня ситуация такая — робот сломался во время торгов на бирже, нужно было за минимальное время решить проблему. Здесь пошагово получилось за 2 минуты…
SQL> connect SYSTEM/34PassworD46@127.0.0.1:1521/xe;
ERROR:
ORA-28001: the password has expired
—Истек срок действия пароля
—Далее просит сменить пароль, меняю пароль сложный
Changing password for SYSTEM
New password: —Ввожу мега сложный пароль.
Retype new password: —Подтверждаю пароль.
ERROR:
ORA-01017: invalid username/password; logon denied
Password unchanged
SQL>
—Вываливается ошибка, пароль не изменен! в чем затык не понимаю.
Пардоныч хлопцы, вопрос решил у меня видимо, что то с ПК видимо, Выполнить cmd > sqlplus /nolog > и далее все что описывал (короче не работает командная строка).
Помогла утилита Oracle RUN SQL Command Line, через нее все тоже самое проделал и все сменилось.
Автору спасибо, как не хватает статей написанных в таком стиле… без лишних отсылок типа «поищите в гугле». Просто, конкретно и понятно.
Полдня лазания по форумам ораклистов не дали никакого понимания. Тут за 2 минуты все понял.
Однако, если статус пользователя EXPIRED, т.е. пароль истек уже, то смена пароля ему почему-то не помогает
Через SQL Developer удалось смениь пароль. Непредсказуемо
ORA-28001: The password has expired
I’m facing the problem about my database in Oracle, When I opened my database, it appear a message with: «ORA-28001: The password has expired»
I have updated my account with somes:
I check and see that, my account: ‘system’ has OPEN, but I open it on Oracle SQL Developer, it still have alert:
I have refered very much links but it still same the problem, how to fix this?
12 Answers 12
I have fixed the problem, just need to check:
Try to connect with the users in SQL Plus, whose password has expired. it will prompt for the new password. Enter the new password and confirm password.
Check «PASSWORD_LIFE_TIME» by
Sql > select * from dba_profiles;
Set to Never expire
Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Then reset the password of locked user.
1.open command prompt 2.type sqlplus 3.It will ask Enter Password, you can give old password, it will show password has expired ORA-28001 4.It will ask new password and retype password 5.It will change with new password 6.Go to the sql database and try to connect with new password, it will connect.
I had same problem even after changing the password, it wasn’t getting reflected in SQLDEVELOPER.
Atlast following solved my problem :
sqlplus sys/root as sysdba 
SELECT username, account_status, expiry_date FROM dba_users;
ALTER USER dummy_user IDENTIFIED BY dummy_password;
ALTER USER dummy_user ACCOUNT UNLOCK;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SELECT username, account_status, expiry_date FROM dba_users;
SELECT username, account_status, expiry_date FROM dba_users;
ALTER USER dummy_user IDENTIFIED BY dummy_password;
ALTER USER dummy_user ACCOUNT UNLOCK;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
«ORA-28001: the password has expired» not fixable
I am facing a problem with my production database. The password expired and although I changed the password, it still says it is expired. Even stranger, I have a production web application and a development web application. Both of them access the same database. The production web application works perfectly, and with the development web application I always get:
I already logged into the server and did this:
Here’s the information requested by Jim:
9 Answers 9
Assuming the Oracle DB (should work for Oracle-XE’s SAMPLE as well) is on Unix, ssh-in and :
I am 100% sure that my config is correct. I was overwriting all local data with the files from the productive app server. Still no success.
The problem is also this password expiry problem came suddenly while developing, so I am sure that I did not change anything.
However, I logged into the test system and reset the password there. My test system contains like 100 rows, my productive app like 1 million, so I can definitely tell you that I am on the right database.
After resetting the password of the test system, I can log-in again! So this whole story is very strange. Thanks for the support.
Can you post the results of the following? Also, what version of Oracle are you using? I’m assuming 11G?
I’d be curious to see what profile you’re using, and what your settings are.
Even I was facing same problem. Issue got resolved after following these below mentioned steps,
select username, profile, account_status, expiry_date from dba_users;
alter profile DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;
How to Unexpire a Password in Oracle (ORA-28001: the password has expired)
What This Error Means
You’re trying to login to an Oracle database and you get ORA-28001: the password has expired. Yikes! This means that the user’s profile forces them to periodically change their password, due to a security precaution. So, if you’re getting this error, it means a password change is overdue. Oracle is trying to force you to change your password to something different so that your database remains secure.
There are times when this feature is an annoyance, because sometimes changing your password is more trouble than it’s worth. So, here’s a workaround that will let you unexpire your password without having to change it! Or, even know what the password is!!
How to Fix The Issue Now
In the below block of code, you’ll want to replace PUT_USERNAME_YOU_WANT_TO_UNEXPIRE_HERE with whomever you want to unexpire the password for.
Run the block of code, and your account will be unlocked and the password unexpired.
How to Prevent “ORA-28001: the password has expired” From Happening In The Future
You say you want to prevent your password from expiring in the future? Not problem, because all you need is a profile that prevents the password from expiring.
The safest way to do this is to create a new profile that has all the same settings as the old profile, but with one change. The new profile should have the PASSWORD_LIFE_TIME set to UNLIMITED.
Below is a script that can help you create the new profile. Just replace USERNAME_GOES_HERE with whatever username you wish to have no password expiration. Optionally, you can change the name of the profile that you’re creating. In the code snippet below, I called it NO_PASSWORD_EXPIRE, but you can change the script so that it’s more meaningful to you.
Free Oracle SQL Tuning Guide
Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!