To set password limit Unlimited in Oracle 11G.

When You  tried login your user(TEST) it is showing password will expired with 7 Days.


[oracle@k44-db1]$ sqlplus test/test

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:34:42 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password: 
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> 


Get to know which profile the test user is using from dba_users view,

SQL> SELECT profile FROM dba_users WHERE username='TEST';

USERNAME PROFILE
-------- ---------- 
TEST     DEFAULT

Now check the password lifetime of the default profile from dba_profiles

SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

and than set default profile limit to unlimited for below query,

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

after profile limit changed  and Alter user(TEST) password for the below statement,

SQL> alter user test identified by test;

User altered.

after that alter username  and try to connect test user,

[oracle@k44-db1]$ sqlplus test/test

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 11:50:15 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> 

Thank and Advance......