Oracle XE: reset locked users

calendar Feb 13, 2015 · 2 min read · oracle  ·
Share on: linkedin copy

If you haven't used an Oracle database for some time you might stumble upon the issue I just had: all internal user accounts were locked and I also didn't know the passwords.

By default login information of internal users are invalid after 180 days.

The following accounts are important to me on my Oracle 11gR2 Database Express Edition (XE):

  • XDB - Oracle XML DB user, required for APEX (Application Express)
  • SYS or SYSTEM - system user

The status of particular users can be gathered using sqlplus. Because I didn't know the login information of the system user but the database was still alive I had to establish a connection using the oracle user. After switching the user context using su it is sufficient to read the appropriate Oracle profile before using sqlplus. During this the required Oracle variables (ORACLE_SID, ORACLE_HOME) are set automatically.

 1# su - oracle
 2$ source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
 3$ echo $ORACLE_SID
 4XE
 5$ sqlplus / as sysdba
 6SQL> select username, account_status from dba_users where username IN ('XDB','SYSTEM','SYS');
 7
 8USERNAME                ACCOUNT_STATUS
 9--------------------------------------
10SYSTEM                EXPIRED & LOCKED
11SYS                   EXPIRED & LOCKED
12XDB                   EXPIRED & LOCKED

As envisaged the appropriate users were locked. Run the following commands to fix this:

1SQL> alter user system account unlock;
2SQL> alter user sys account unlock;
3SQL> alter user xdb account unlock

Afterwards you can also set new passwords:

1SQL> alter user system identified by PASSWORD;
2SQL> alter user sys identified by PASSWORD;
3SQL> alter user xdb identified by PASSWORD;

Of course you should replace PASSWORD with your own password. 😉

Another possibility is to disable automatic locking accounts after 180 days:

1SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

I'd like to thank Thorsten for the last tip! 🙂

Translations: