account_expired_oracle.md

ORA-28000 : the account is locked in oracle

The following is solution to change username and password in oracle db system. The system requires you have access to system account to reset other account.

I tested oracle docker image with command

docker run --rm -v oracle_data:/u01/app/oracle  -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true epiclabs/docker-oracle-xe-11g

Which is

• -v option stores data in volume after container stopped.
• default username/password for system user is system/oracle

The following is an error that I get when connect to oracle DB.

import logging
import os

logging.basicConfig(level=logging.DEBUG)
import cx_Oracle

DB_ADDR = os.getenv("DB_ADDR", "127.0.0.1")
DB_PORT = os.getenv("DB_PORT", 1521)
DB_SID = os.getenv("DB_SERVICE", "xe")
DB_NAME = os.getenv("DB_NAME", "hr")  # connect to HR db


DB_USER = os.getenv("DB_USER", "system")
DB_PWD = os.getenv("DB_PWD", "1")

POOL_MIN_SIZE = int(os.getenv("POOL_MIN_SIZE", 2))
POOL_MAX_SIZE = int(os.getenv("POOL_MAX_SIZE", 10))

LOG = logging.getLogger(__name__)


dsn = cx_Oracle.makedsn(DB_ADDR, DB_PORT, DB_SID)
LOG.info("make dsn: %s", dsn)
LOG.info("connecting to oracle db")
pool = cx_Oracle.SessionPool(
    DB_USER,
    DB_PWD,
    dsn,
    min=POOL_MIN_SIZE,
    max=POOL_MAX_SIZE,
    increment=1,
    encoding="UTF-8",
)
LOG.info("connected to oracle db with pool: %s", pool)

The code raises the following exception:

(py3) λ  tools git:(master) ✗ python connect.py
Traceback (most recent call last):
  File "connect.py", line 22, in <module>
    connection = cx_Oracle.connect(DB_NAMESPACE, DB_USER, dsn, encoding="UTF-8")
cx_Oracle.DatabaseError: ORA-28000: the account is locked

I connect to ORACLE db by system and I got

SQL> select username,account_status from dba_users;

USERNAME               ACCOUNT_STATUS
------------------------------ --------------------------------
SYSTEM                   OPEN
SYS                       OPEN
ANONYMOUS               OPEN
APEX_PUBLIC_USER       LOCKED
APEX_040000               LOCKED
OUTLN                   EXPIRED & LOCKED
XS$NULL                EXPIRED & LOCKED
XDB                       EXPIRED & LOCKED
CTXSYS                   EXPIRED & LOCKED
MDSYS                   EXPIRED & LOCKED
FLOWS_FILES               EXPIRED & LOCKED

USERNAME               ACCOUNT_STATUS
------------------------------ --------------------------------
HR                       EXPIRED & LOCKED

Unlock user hr

SQL> select username,account_status from dba_users;

USERNAME               ACCOUNT_STATUS
------------------------------ --------------------------------
SYSTEM                   OPEN
SYS                   OPEN
ANONYMOUS               OPEN
APEX_PUBLIC_USER           LOCKED
APEX_040000               LOCKED
OUTLN                   EXPIRED & LOCKED
XS$NULL                EXPIRED & LOCKED
XDB                   EXPIRED & LOCKED
CTXSYS                   EXPIRED & LOCKED
MDSYS                   EXPIRED & LOCKED
FLOWS_FILES               EXPIRED & LOCKED

USERNAME               ACCOUNT_STATUS
------------------------------ --------------------------------
HR                   EXPIRED & LOCKED

12 rows selected.

SQL> alter user hr account unlock; 

User altered.

SQL> select username,account_status from dba_users;

USERNAME		       ACCOUNT_STATUS
------------------------------ --------------------------------
SYSTEM			       OPEN
SYS			       OPEN
ANONYMOUS		       OPEN
HR			       EXPIRED
APEX_PUBLIC_USER	       LOCKED
APEX_040000		       LOCKED
OUTLN			       EXPIRED & LOCKED
XS$NULL 		       EXPIRED & LOCKED
XDB			       EXPIRED & LOCKED
CTXSYS			       EXPIRED & LOCKED
MDSYS			       EXPIRED & LOCKED

USERNAME	           ACCOUNT_STATUS
------------------------------ --------------------------------
FLOWS_FILES               EXPIRED & LOCKED

12 rows selected.