[OracleDB11g] Check if a Schema is LOCKED/OPEN

Here is a small tip on how to check if a Schema in a particular database is Locked or Open.

There are two ways to do this. First step is to try connecting to this schema if you remember the password for the particular schema and if your password is right, you will get the following error,

ORA-28000: the account is locked

The second step is to be followed if you don't remember the password for this schema. Connect to Database as SYSDBA user.

After connecting to the database run the following query to retrieve details of schema's that are currently locked,

SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE FROM DBA_USERS WHERE ACCOUNT_STATUS NOT IN ('OPEN')

Output:

USERNAME	ACCOUNT_STATUS	LOCK_DATE
SCOTT	EXPIRED & LOCKED	06-AUG-14
OE	EXPIRED
OLAPSYS	EXPIRED & LOCKED	30-MAR-10

See Also: How to Unlock a locked Schema ?