Manage the users and resetting its password in Oracle

Check the status of User in Oracle:

SELECT username, account_status FROM dba_users WHERE username = '122486';

Change the password of user in oracle:

ALTER USER "122486" IDENTIFIED BY Wednesday14;

Change the status of user to locked:

ALTER USER username ACCOUNT LOCK;

Change the status of user to unlocked:

ALTER USER username ACCOUNT UNLOCK;

Check the expired account detail:

SELECT username, account_status FROM dba_users WHERE account_status LIKE '%EXPIRED%';

Unexpire the account:

ALTER USER username IDENTIFIED BY password;

Disable the default password expiry:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Backup of the old password in Oracle before change:

In 10g:
SELECT 'ALTER USER '||username||' IDENTIFIED BY VALUES '''||password||'';' end FROM dba_users;

In 11g:
SELECT 'ALTER USER '||a.username||' IDENTIFIED BY VALUES '''||b.password||'';'  FROM dba_users a,sys.user$ b WHERE b.name = a.username;


Steps for handle the ORA-28000: the account is locked

For Unlock the account:

ALTER USER username ACCOUNT UNLOCK;

Check password policy user is using for permanent solution:

select profile from dba_users where username = 'DBUSER';

Change the profile resource to unlimited value from 180 days:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;