28.10.07

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');
SQL>commit;
And of course, this topic is covered by the Application Express User's Guide.

3 comments:

Patrick Wolf said...

Any idea why SCOTT is now a restricted schema? Isn't it THE test/example schema?

Patrick

Dimitri Gielis said...

Nice post Jornica.

Patrick, HR became "the" test/example schema, because you've more "possibilities" into that schema.

Dimitri

Jornica said...

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