Oracle unlocking and locking (for HR Users)
SQL * Plus: Release 9.2.0.4.0-production on TUE Jul 14 18:12:38 2009
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
SQL> conn sys/sys as sysdba
Connected.
SQL> show user
User is "sys"
SQL> DESC dba_users
Name null? Type
-----------------------------------------------------------------------------
Username not null varchar2 (30)
User_id not null number
Password varchar2 (30)
Account_status not null varchar2 (32)
Lock_date date
Expiry_date date
Default_tablespace not null varchar2 (30)
Temporary_tablespace not null varchar2 (30)
Created not null date
Profile not null varchar2 (30)
Initial_rsrc_consumer_group varchar2 (30)
External_name varchar2 (4000)
SQL> set lines 200
SQL> select username, account_status from dba_users;
Username account_status
--------------------------------------------------------------
Sys open
System open
Dbsnmp open
Scott open
Outln expired & locked
Wmsys expired & locked
Ordsys expired & locked
Ordplugins expired & locked
MDSYS expired & locked
Ctxsys expired & locked
XDB expired & locked
Username account_status
--------------------------------------------------------------
Anonymous expired & locked
Wksys expired & locked
Wkproxy expired & locked
ODM expired & locked
Odm_mtr expired & locked
Olapsys expired & locked
HR expired & locked
OE expired & locked
PM expired & locked
SQL> alter user HR account unlock; (unlock)
User altered.
SQL> select username, account_status from dba_users;
Username account_status
--------------------------------------------------------------
Sys open
System open
Dbsnmp open
Scott open
HR expired
Outln expired & locked
Wmsys expired & locked
Ordsys expired & locked
Ordplugins expired & locked
QL> alter user HR identified by HR; (Change Password)
User altered.
SQL> select username, account_status from dba_users;
Username account_status
--------------------------------------------------------------
Sys open
System open
Dbsnmp open
Scott open
HR open
Outln expired & locked
SQL> conn HR/hr
Connected.
SQL> Conn/As sysdba
Connected.
SQL> alter user HR account lock; (LOCK)
User altered.
SQL> select username, account_status from dba_users;
Username account_status
--------------------------------------------------------------
Sys open
System open
Dbsnmp open
Scott open
HR locked
Outln expired & locked
SQL> alter user HR password expire; (the password is invalid)
User altered.
SQL> select username, account_status from dba_users;
Username account_status
--------------------------------------------------------------
Sys open
System open
Dbsnmp open
Scott open
Outln expired & locked
Wmsys expired & locked
Olapsys expired & locked
HR expired & locked
OE expired & locked
PM expired & locked
SQL> conn HR/hr
Error:
ORA-28000: the account is locked
Warning: you are no longer connected to Oracle.
Contributed:
Circle