Where is the Scott schema in APEX?
I decided to install Oracle 11g. And with Oracle 11g Application Express (APEX)is installed by default. Before importing an application based on the emp
table, I decided to create a workspace based on the existing schema Scott
. However the administrative interface did not allow me to select Scott
. By not using the LOV but typing the schema name, APEX gave the following error: Schema is reserved or restricted
. What is going on?
APEX uses the following query to populate the list of values:
SELECT name n,
name r
FROM sys.user$ u
WHERE type# = 1
AND name <> 'FLOWS_030000'
AND name NOT IN('HTMLDB_PUBLIC_USER', 'PUBLIC_USER', 'FLOWS_FILES',
'SCHEDULER', 'PEOPLE', 'APEX_PUBLIC_USER')
AND name NOT IN
(SELECT SCHEMA
FROM wwv_flow_restricted_schemas
WHERE NOT EXISTS
(SELECT 1
FROM wwv_flow_rschema_exceptions
WHERE schema_id =
(SELECT id
FROM wwv_flow_restricted_schemas
WHERE SCHEMA = u.name)
)
)
ORDER BY 1
/
Beside some hard coded schema names, there are two flows_030000
tables used in the query, wwv_flow_restricted_schemas
and wwv_flows_rschema_exception
.
SQL>SELECT * FROM flows_030000.wwv_flow_restricted_schemas t
/
ID SCHEMA CREATED_BY CREATED_ON LAST_UPDATED_BY LAST_UPDATED_ON
-- ------ ---------- ---------------- --------------- ---------------
...
38 SCOTT SYS 3-8-2007 2:12:53
...
The wwv_flows_rschema_exception
contains no rows.
Both tables are used by the package htmldb_site_admin_privs
, which serves as an interface packages. Now remove the restriction on Scott
by executing the following statements (as Sys
).
SQL>exec flows_030000.htmldb_site_admin_privs.unrestrict_schema(p_schema => 'SCOTT');And of course, this topic is covered by the Application Express User's Guide.
SQL>commit;
3 comments:
Any idea why SCOTT is now a restricted schema? Isn't it THE test/example schema?
Patrick
Nice post Jornica.
Patrick, HR became "the" test/example schema, because you've more "possibilities" into that schema.
Dimitri
Dear APEX evangelists :-),
SCOTT is history, HR and others are the sample schema's now. Read more over the sample schema's here:
http://www.databasejournal.com/features/oracle/article.php/3364161
But the question why SCOTT is excluded (in APEX) is still unanswered...
With kind regards,
Jornica
Post a Comment