Reset database Account_Status from Expired(Grace) to OPEN

We know that in database we have different profiles implemented which let our database password expired after sometime {default after 180 days}. Usually it is good practice to change your database users password on regular interval.

Question comes, what If I do not want to change password and reuse the existing one, well in this blog I have explain steps how you can again reset some user’s password to the old one. In my company we sometime we get request when user complaint us the his password has been expired and he wants to re-set the password to same old one.

Here, there is possibility that you might not know his old password. Still you can set his password to the old one.

Step 1: Check and Verify If account password is Expired.

If you know the database username {dksharma} well and good, you can use below mentioned SQL.

select username,account_status from dba_users where username like ‘dks%’

else in general you can verify putting “account_status” under WHERE clause.

select username, account_status from dba_users where account_status like ‘%EXPIRED%’

Once you have verified that, account_status is “EXPIRED(GRACE)”, proceed to next step.

Step 2: Fetching existing password of user.

Now depending on the database version, you query may vary:

For 10g or lower use:

select password from dba_users where username = ‘[username]’

For 11g and higher use:

select spare4 from sys.user$ where name = ‘[username]’

The output of above query will give you encrypted password of the user.

Step 3: Use this encrypted password to set Account_Status to OPEN by resetting it.

alter user [username] identified by values ‘output of query in Step 2’;

Following three simple steps you can reset the old password of any user, without even knowing the actual password.

For sure it will help you somewhere or sometime in your DBA life.

One thought on “Reset database Account_Status from Expired(Grace) to OPEN

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s