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'
or
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
alter user USERNAME profile PROFILE_THAT_DOES_NOT_EXPIRE;
To execute all these SQL statements, the user you are logged in with should have the right privileges.