changing oracle user profile and expired status


I recently had an oracle user(schema) that was created with a profile of 'DEFAULT' which meant the user's password would expire. The user should have been created with a profile that does not expire its password. Well, the password expired and I have to change the password without actually changing it.


select * from dba_users where username = 'USERNAME';


The SQL above showed the user's information. The password had expired (ACCOUNT_STATUS).  


The first thing i did was to get the user's current password.  Depending on your oracle database version you can try the following SQL statements.



select password from dba_users where username = 'USERNAME'




select dbms_metadata.get_ddl ('USER', 'USERNAME')from dual;


Copy the password and execute the SQL below.


alter user USERNAME identified by values 'password_copied_from_previous_sql_result';



This SQL above will set the user's ACCOUNT_STATUS to open.


The change the user's profile to a profile that does not expire, executed the SQL below





To execute all these SQL statements, the user you are logged in with should have the right privileges.


Add new comment