Mir Sayeed Hassan – Oracle Blog

How to reopen the expired db account without changing the password

Consider you have an expired db account & you need to recover this acccount at any cost as per your client request, Yes its possible to recover it, Find the below scenario tested in our env

Create a test user in database & try to expire this user & recover it

sys@TESTDB> create user mir identified by mir123;

User created.

sys@TESTDB> grant connect to mir;

Grant succeeded.

sys@TESTDB> connect mir/mir123456;

Connected.

mir@TESTDB> show user;

USER is "MIR"

sys@TESTDB> select username, account_status, expiry_date from dba_users where username='MIR';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA

------------------------------ -------------------------------- ---------

MIR                            OPEN                             11-APR-19

Now try to expire the user manually for only testing purpose

sys@TESTDB> alter user mir password expire;

User altered.

Verify the user status

sys@TESTDB> select username, account_status, expiry_date from dba_users where username='MIR';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA

------------------------------ -------------------------------- ---------

MIR                            EXPIRED                          13-OCT-18

Retrive the existing password

sys@TESTDB> select dbms_metadata.get_ddl('USER','MIR') from dual;

DBMS_METADATA.GET_DDL('USER','MIR')

-------------------------------------------------------------------------------

CREATE USER "MIR" IDENTIFIED BY VALUES 'S:1FA05D7EA87C1DBA8BC629D7CD0BA1A9B8

Now edit the above given password & alter it

sys@TESTDB> alter user mir identified by values ' S:1FA05D7EA87C1DBA8BC629D7CD0BA1A9B8';

alter user mir identified by values 'S:1FA05D7EA87C1DBA8BC629D7CD0BA1A9B8'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzsviver:4], [], [], [], [], [],
[], [], [], [], [], []

If you get the above error, there might be a additional line or character is copied, Issue the correct password & try

sys@TESTDB> alter user mir identified by values ' S:1FA05D7EA87C1DBA8BC629D7CD0BA1A9B8';
User altered.

Verify

sys@TESTDB> select username, account_status, expiry_date from dba_users where username='MIR';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA

------------------------------ -------------------------------- ---------

MIR                            OPEN                             11-APR-19

Or

mir@TESTDB> conn mir

Enter password:

Connected.

mir@TESTDB> sho user

USER is "MIR"

============Hence tested & verified in our test env================