Tag: ORA-06512

ORA-20987: APEX – Schema is restricted – Contact your application administrator

On By windows7bugsIn APEXLeave a comment

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.

Oracle “directory” object | OS level permissions

On By windows7bugsIn Linux, Oracle Applications(R12), Oracle database 11gLeave a comment

We are using Oracle EBS R12 and for a custom module, wanted to log the Oracle seeded API outputs to custom log files for later scrutiny & error corrections.

Following the standard procedure, create the folder like below

create or replace directory OMS_LOGS as '/u01/applmgr/oms_logs'

and started testing the directory using SQL Developer where I am logged in as APPS user

I kept getting the below errors

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 8

and the detailed errors said this could be due to OS level access permissions. Luckily I landed on a stackoverflow discussion & one of the answers clearly said this error “could” be due to the file/folder permissions for user “oracle”.

As “root” I changed the permissions for the path “/u01/applmgr/oms_logs” like below

chmod g+w /u01/applmgr/oms_logs

and that fixed the invalid file operation errors. Hope this helps someone out there!