Oracle APEX: Database Credential Error

If you are using Oracle APEX docker locally, you might encounter the following error.

ORDS was unable to make a connection to the database. The database user specified by db.username configuration setting is expired. The connection pool named: |default|lo| had the following error(s): ORA-28001: The account has expired. The password must be changed.

First let connect to our database as sysdba. You can use SQL Developer or just use the Terminal.

docker exec -it «container_id» /bin/bash

sqlplus / as sysdba

ALTER SESSION SET CONTAINER = FREEPDB1; #XEPDB1 for express

If we check our DBA_USERS with:

select * from DBA_USERS where ACCOUNT_STATUS = 'EXPIRED';

We should see all the database users whose password has been expired, including ORDS_PUBLIC_USER. We could solve this issue changing the password, however this would also require the change in ORDS configuration for APEX.

Instead we would like to set the ACCOUNT_STATUS to OPEN without changing the password. This can be done by providing the same password, however we might not know what the password was set to. Instead we can create a SQL statement by executing:

SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' as sql2execute FROM sys.user$ WHERE name='ORDS_PUBLIC_USER';

This will provide you the SQL statement that you should execute like:

ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY VALUES 'S:15ED52D32554831E2A03656B9091A52765EBB3C6E74BBB483BE5190D6B50;T:3B0F75432BA387C46E59D88C2A48297AAD5832139E6F8C43C9EA492DF15F51DC13BBF8EB6B45DEE777EB0118D0BF022BEADE80D1D14066676C40EC63E49DEB6869B6A0E336C25E42A513CBA96AF9EF22;';

Just copy the given result and execute it.

You should now see that the account status has been changed to OPEN. Restarting the docker will cause Oracle APEX to load properly.

BONUS (since my docker is used only for development):

You can turn off expiration completely using:

alter profile "DEFAULT" limit password_life_time UNLIMITED;