Problem using sample schemas by Oracle in Oracle APEX

Question

I am new to Oracle and Oracle APEX. I have been practicing to make Oracle APEX database application using "hr" schema of the database. Before doing so, I unlocked the schema, along with schemas "scott", "pm", "sh" - so that I can work using them later.

Now I am trying to make an application using "scott" schema. The schema is already unlocked, so I can connect to this schema using sqlplus or TOAD.

C:\Users\srl13>sqlplus scott/scott

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 7 15:39:23 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to create a workspace, I cannot select "scott" as the active schema for the workspace - as the workspace is not shown in the list of existing schemas.

If I manually input "scott" in the "Schema Name" and try to proceed, I get the error

Schema is reserved or restricted

I don't know what went wrong and what's the problem - as I already did same things before making APEX application on "hr" schema. Can you suggest me what to do so that I can make the workspace? I also noticed that although I unlocked a number of schemas, no schema except "hr" and "bi" is shown in the schema selection list; so I basically can't make any workspace on any of the unlocked schemas except "hr" and "bi" - and I presume that I am going to face same error if I try to use those schemas "manually".

How should I solve this issue, so that I can create the workspace(s)?

Answer 1

You can unrestrict it.

You just need to run the APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA proc.

EXEC FLOWS_XXXXXX.APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA(p_schema => 'SCOTT');

Replace FLOWS_XXXXXX with the name of your APEX schema.

You can also check which schemas are restricted:

SELECT * FROM flows_XXXXXX.wwv_flow_restricted_schemas;

Documentation link.