Monday, June 29, 2015

How to re-open EXPIRED & LOCKED user with the same password

Hi All ...

Here the fast way to re-open the DB users when it in  EXPIRED & LOCKED status and you don't want to change the password and not remember the old one.

Run the following with "/as sysdba" :

alter profile default limit failed_login_attempts unlimited password_life_time unlimited;

2. Sql to change the status from LOCKED to OPEN  (copy the result and run in sqlplus).

select 'alter user '|| username || ' account unlock;' 
from dba_users where account_status = 'LOCKED'

3. Sql to remove the expired status (copy the result and run in sqlplus).
select 'alter user ' || || ' identified by values' || ' ''' || spare4 || ';' || su.password || ''';'
from sys.user$ su join dba_users du on ACCOUNT_STATUS like 'EXPIRED%'
and = du.username;

Good Luck ...

No comments:

Post a Comment