To set password limit Unlimited in Oracle Database

Sometimes for a test user in oracle , we often get this error :-

SQL> conn scott/tiger
ERROR:
ORA-28002: the password will expire in 7 days
Connected

We want that as its a test user, its password never expire. We have to make a change in the profile associated with this user.

SQL> select profile from dba_users where username='SCOTT';

PROFILE
------------------------------------------------------------------------------------------
PROFILE



SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';


PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------- ------------------------------ ------------------------ ------------------------------
PROFILE PASSWORD_LIFE_TIME PASSWORD 10


we need to change the limit of password_life_time parameter to unlimited.


SQL> alter profile PROFILE limit PASSWORD_LIFE_TIME unlimited;


Profile altered.


SQL> select * from dba_profiles where profile='PROFILE' and RESOURCE_NAME='PASSWORD_LIFE_TIME';


PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------- ------------------------------ ------------------------ ------------------------------
PROFILE PASSWORD_LIFE_TIME PASSWORD UNLIMITED


Check that the expirary date column is null of desired user in DBA_USERS view


SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from dba_users where USERNAME='SCOTT';


USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
------------------------------ ------------------------- ------------------ -------------------------
SCOTT OPEN PROFILE


Similarly if we want failed login attempts has to be unlimited for a test user then alter the profile associated with the test user :-


SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;


Profile altered


I hope this article helped you.