I went to use my APEX+ORDS All in one Docker and noticed parts of APEX weren't working. Sometimes ORDS would throw an error, sometimes images wouldn't load. It was annoying
I found that the problem was that user ORDS_PUBLIC_USER had expired & I needed to fix that
Form a DB Connection
Since I use the Docker image, I have to connect this way.
Enter bash
docker exec -it 23cfree /bin/bashOpen SQL*Plus with sqlplus / as sysdba
sqlplus / as sysdbalike this...
bash-4.4$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed Apr 5 13:27:32 2023 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0Set the PDB
ALTER SESSION SET CONTAINER = FREEPDB1;
Finds & Unexpire the Users
Find the Expired Users
COLUMN username FORMAT A32 COLUMN account_status FORMAT A32 select username, account_status from dba_users where username like 'APEX%' or username like 'ORDS%';This will show something like
USERNAME ACCOUNT_STATUS -------------------------------- -------------------------------- APEX_LISTENER OPEN APEX_PUBLIC_USER OPEN APEX_REST_PUBLIC_USER OPEN ORDS_METADATA OPEN ORDS_PUBLIC_USER EXPIRED APEX_230100 LOCKED APEX_230200 LOCKED 7 rows selected.So we are going to fix ORDS_PUBLIC_USER as this has expired.
Adjust the username in the Declare section as appropriate & run the following
DECLARE CONST_USERNAME CONSTANT VARCHAR2(128) := 'ORDS_PUBLIC_USER'; v_unexpire_command VARCHAR2(4000); BEGIN EXECUTE IMMEDIATE 'ALTER USER ' || CONST_USERNAME || ' ACCOUNT UNLOCK'; SELECT 'alter user ' || name || q'< identified by values '>' || spare4 || ';' || password || q'<'>' INTO v_unexpire_command FROM sys.user$ WHERE name=CONST_USERNAME; EXECUTE IMMEDIATE v_unexpire_command; END; /Credits to TuningSQL.com
Test the user has been unexpired
COLUMN username FORMAT A32 COLUMN account_status FORMAT A32 select username, account_status from dba_users where username like 'APEX%' or username like 'ORDS%';We should now see
USERNAME ACCOUNT_STATUS -------------------------------- -------------------------------- APEX_LISTENER OPEN APEX_PUBLIC_USER OPEN APEX_REST_PUBLIC_USER OPEN ORDS_PUBLIC_USER OPEN ORDS_METADATA OPEN APEX_230100 LOCKED APEX_230200 LOCKED 7 rows selected.
Stop it happening again
Adjust the first line, to set the User you wish to assign to a non-expiring profile.
DECLARE
CONST_USERNAME constant varchar2(128) := 'ORDS_PUBLIC_USER';
CONST_NEW_PROFILE_NAME constant varchar2(128) := 'NO_PASSWORD_EXPIRE';
v_existing_profile varchar2(128);
v_sql varchar2(32767);
begin
select du.profile into v_existing_profile
from dba_users du
where du.username = CONST_USERNAME;
v_sql := 'create profile ' ||
CONST_NEW_PROFILE_NAME ||
' limit ';
for v_row in (select dp.resource_name, dp.limit
from dba_profiles dp
where dp.profile = v_existing_profile
and dp.resource_name <> 'PASSWORD_LIFE_TIME')
loop
v_sql := v_sql || v_row.resource_name || ' ' || v_row.limit || ' ';
end loop;
v_sql := v_sql || 'PASSWORD_LIFE_TIME UNLIMITED';
declare
profile_already_exists exception;
pragma exception_init(profile_already_exists, -2379);
begin
execute immediate v_sql;
exception when profile_already_exists then
null;
end;
execute immediate 'alter user ' || CONST_USERNAME || ' profile ' || CONST_NEW_PROFILE_NAME;
end;
/
Check the profile moved to the non expiring profile Credits to TuningSQL.com
COLUMN username FORMAT A24 COLUMN account_status FORMAT A16 COLUMN profile FORMAT A24 select username, account_status, profile from dba_users where username like 'APEX%' or username like 'ORDS%';Its should have moved it to the new profile
USERNAME ACCOUNT_STATUS PROFILE ------------------------ ---------------- ------------------------ APEX_LISTENER OPEN DEFAULT APEX_PUBLIC_USER OPEN DEFAULT APEX_REST_PUBLIC_USER OPEN DEFAULT ORDS_METADATA OPEN DEFAULT ORDS_PUBLIC_USER OPEN NO_PASSWORD_EXPIRE APEX_230100 LOCKED DEFAULT APEX_230200 LOCKED DEFAULT
ENJOY!
Whats the picture? It's John St in Harrogate one cold morning. Visit Yorkshire!