[OracleDB11g] Unlock a Locked/Expired Schema

You have a locked Schema in a database. Say for example the user SCOTT in Oracle Database 11g.

Try connecting to the SCOTT schema using the default password: TIGER and you will get the following error

ORA-28000: the account is locked

To unlock the account run the following query

ALTER USER SCOTT ACCOUNT UNLOCK;

Now the schema SCOTT would be unlocked and you can connect to the schema.

You can also change the password of the schema if you wish by running the below query

ALTER USER SCOTT IDENTIFIED BY TIGER1 ACCOUNT UNLOCK;

This query will both unlock the SCOTT schema and change it's password to 'TIGER1'. Now you can connect to the SCOTT schema using the new password.

[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 ?

Why Discover BI ?

This blog is my effort at learning and documenting the things i have come across in my short career as a BI Developer. 'Learn and Grow' is the motive behind this blog. The content here are my understanding of how BI works and by publishing it to a wider audience I expect myself to become more responsible.

There is so much to BI that i am yet to learn. This blog will be a living document of my learnings and i hope that my readers would be able to benefit from reading this.