How to change account_status in dba_users

[pcotten]

I have a problem. I need to change account_status to open for one user whose account_status was set to EXPIRED by mistake (by assigning a profile with verify_function). If I knew the password I could chnage the password to the same password and be done with. But I don't know the password. Is there a way to manually update account_status column in dba_users to show it OPEN and not EXPIRED?

Another user has account_status showing EXPIRED(GRACE). I need that user to show OPEN. Fortunaltely I know the password. I am guessing I can reset the password to the same password like alter user test identified by summer;

Thanks for the help.

[Thomasps]

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS
--------------------------------
OPEN
SQL> alter user thomas account lock;

User altered.

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS
--------------------------------
LOCKED

SQL> alter user thomas account unlock;

User altered.

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS
--------------------------------
OPEN
SQL> alter user thomas password expire;

User altered.

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS
--------------------------------
EXPIRED
SQL> alter user thomas identified by thomas;

User altered.

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS
--------------------------------
OPEN

Otherwise. no way...

[Thomasps]

Hay... This worked

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS
--------------------------------
EXPIRED

SQL> select password from dba_users where username='THOMAS';

PASSWORD
------------------------------
063D8DE086C2860D

SQL> alter user thomas identified by values '063D8DE086C2860D';

User altered.

SQL> select account_status from dba_users where username='THOMAS';

ACCOUNT_STATUS
--------------------------------
OPEN
SQL> conn thomas/thomas
Connected.

[pcotten]

That is a problem. I can't reset password. This is a generic account that is used by cognos and if I reset password, it will need to be changed somewhere and we don't know where exactly it is harcoded. I have to be able to change the account status back to OPEN from EXPIRED.

[Thomasps]

The second one should work.. ???

[pcotten]

I created a new account and expired password and tried your solution. it worked. Should work on any account right? The password values for my account in question that needs to be "un-expired" is 43BA7BBC412AE7CB. If I change the password like you had shown, it should show account_status as open, right?

alter user test identified by values '43BA7BBC412AE7CB';

[Thomasps]

Yes. but, you need not create a new account for this. Just change the password of the exisitng 'expired' account with 'values' option.

[pcotten]

Created the separate, new account to test it out.

It worked. The account_status is now back to OPEN.

Thanks for your help.

[jhmartin]

It would still be wise to find out what the real password is for that account, and possilbly change it.