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'

 

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.

 

Add new comment