I reinstalled Oracle database 19c & lost the APEX setup I had. I installed and configured APEX with ORDS and was online quickly and wanted to add the additional sample schemas, as this time I “was” “SERIOUS” about learning APEX ;)

After setting up the additional sample schemas, I noticed that the new schemas were not available to add to APEX workspaces! Sample schemas those were installed as part of the DBCA were the only ones available for APEX.

Soon, I realized that it was NOT a very stupid silly situation. There were hardly many articles explaining how to overcome this issue and the closest ones were not on Oracle forums as usual, instead on stackoverflow!

Anyway, I stumbled upon a question about APEX 4 and there was one answer dealing with APEX 5, that is still effective on APEX 23.2. Let’s quickly have a look at it.

Find the schemas that are restricted for APEX. Connect as SYS or SYSTEM

ALTER SESSION SET CONTAINER="ORCL"; --your PDB on which the APEX is installed your sample schemas reside
/
SELECT * FROM APEX_230200.wwv_flow_restricted_schemas;
/

The above should provide you a list of schemas that are restricted. “OE” and other sample schemas that you have installed once after setting up the database must be listed in this listing. Now proceed to derestrict the schema for APEX. We will use the sample schema “OE” for this exercise.

EXEC APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA('OE');
/

EXEC APEX_INSTANCE_ADMIN.ADD_SCHEMA('YOUR_WORKSPACE_NAME', 'OE');
/
-- Don't forget to commit!
commit;
/

That’s all. Once logged in, APEX will show the schema “OE” or all other schemas you have added following the above method under the targeted workspace.