How to create schema for Oracle APEX Workspace?


If you choose to create a schema while creating the workspace in Oracle Application Express administration service, APEX will create a brand new tablespace as well. This may not be an ideal situation. Having too many tablespaces, specially when it is not required, adds management and administrative overhead.

You can use the following script to create the schema and then just use this schema when you are creating the worksace.

— Connect as SYSTEM or DBA User
$ sqlplus system/password@XE

— Create the new Schema/User
CREATE USER APEXAPPS IDENTIFIED BY APEXAPPS
DEFAULT TABLESPACE APEXAPPS_TS
TEMPORARY TABLESPACE TEMP;

— Grant quota on assigned tablespace
ALTER USER APEXAPPS QUOTA 10M ON APEXAPPS_TS ;

— Grant Role Priviledge
GRANT “CONNECT” TO APEXAPPS ;
ALTER USER APEXAPPS DEFAULT ROLE “CONNECT”;

— Grant system privileges
GRANT CREATE JOB TO APEXAPPS ;
GRANT CREATE INDEXTYPE TO APEXAPPS ;
GRANT CREATE SYNONYM TO APEXAPPS ;
GRANT CREATE DIMENSION TO APEXAPPS ;
GRANT CREATE VIEW TO APEXAPPS ;
GRANT CREATE CLUSTER TO APEXAPPS ;
GRANT CREATE MATERIALIZED VIEW TO APEXAPPS ;
GRANT CREATE PROCEDURE TO APEXAPPS ;
GRANT CREATE ANY CONTEXT TO APEXAPPS ;
GRANT CREATE TYPE TO APEXAPPS ;
GRANT CREATE SEQUENCE TO APEXAPPS ;
GRANT CREATE OPERATOR TO APEXAPPS ;
GRANT CREATE TRIGGER TO APEXAPPS ;
GRANT CREATE TABLE TO APEXAPPS ;

— Grant Object Privileges
GRANT EXECUTE ON SYS.DBMS_RLS TO APEXAPPS ;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO APEXAPPS ;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO APEXAPPS ;

These commands will create the schema and assign the same privileges to that schema that gets assigned when you create a new schema while creating the APEX workspace (verified in Oracle APEX 4.1). Once the schema is created specify ‘Yes’ for Re-use existing schema and then specify the schema name.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s