Managing Workspace to Schema Assignments

Instance administrators can view, edit, and remove schema and workspace assignment, associate additional schemas with a workspace, and create new schemas.

When users sign in to Oracle APEX, they access a shared work area called a workspace. Each workspace can have multiple associated (or mapped) schemas. By associating a workspace with a schema, developers in that workspace can create new database objects in that schema and build applications that interact with the database objects in that schema.

See Also: Managing Schemas and Viewing the Parsing Schemas Report

Viewing Schema and Workspace Assignments in a Development Environment

View an existing schema to workspace assignments in a full development environment.

To view an existing schema to workspace assignment:

1. Sign in to Oracle APEX Administration Services.

2. Click Manage Workspaces.

3. Under Workspace Actions, click Manage Workspace to Schema Assignments.

   The Manage Workspace to Schema Assignments page appears, listing all workspaces in your environment along with their associated schemas.

   This page displays as an interactive report. To customize the report, use the Search bar at the top of the page.

Viewing Schema and Workspace Assignments in a Runtime Environment

View the existing schema to workspace assignments in a runtime environment.

To view the existing schema to workspace assignment in a runtime environment:

1. Start SQLcl and connect to the database where Oracle APEX is installed as SYS, for example:

   • On Windows:

     SYSTEM_DRIVE:\ sql /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password

   • On UNIX and Linux:

     $ sql /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password

2. Run the following statement:

   SELECT APEX_INSTANCE_ADMIN.GET_SCHEMAS(WORKSPACE_NAME)
   FROM DUAL;

   Where WORKSPACE_NAME is the name of the workspace.

Editing Existing Schema and Workspace Assignments

Edit existing schema and workspace assignments.

To edit an existing schema and workspace assignment:

1. Sign in to Administration Services.

2. Click Manage Workspaces.

3. Under Workspace Actions, click Manage Workspace to Schema Assignments

   The Manage Workspace to Schema Assignments page appears.

4. To edit an existing workspace to schema assignment:

   1. Select the workspace name.

   2. Select a new workspace or database schema.

   3. Click Apply Changes.

Associating Schemas in a Full Development Environment

Associate existing or new schemas with a workspace on the Manage Workspace to Schema Assignments page.

To associate additional schemas with a workspace:

1. Sign in to Administration Services.

2. Click Manage Workspaces.

3. Under Workspace Actions, click Manage Workspace to Schema Assignments.

   The Manage Workspace to Schema Assignments page appears.

4. Click Add Schema.

   The Add Schema wizard appears.

5. The schema is a - Identify if the schema to be provisioned for the workspace exists or is new.

   1. Select Existing or New.

   2. Click Next.

   The UI changes depending upon your selection.

6. If The schema is a is Existing:

   1. Workspace - Enter the name of the workspace, or select one from the list.

   2. Click Next.

   3. Schema - Enter new schema name.

   4. Grant APEX Privileges - Leave Grant APEX Privileges on to grant all privileges needed by APEX to the schema.

   5. Click Next.

7. If The schema is a is New:

   1. Workspace - Enter the name of the workspace, or select one from the list.

   2. Click Next.

   3. Schema - Enter new schema name.

   4. Password - Enter a password for schema.

   5. Default Tablespace - Enter the default tablespace for the provisioned schema, or select one from the list.

   6. Temporary Tablespace - Enter the temporary tablespace for the provisioned schema, or select one from the list.

   7. Grant APEX Privileges - Leave Grant APEX Privileges on to grant all privileges needed by APEX to the schema.

   8. Click Next.

8. Review your selections and click Add Schema.

Associating Additional Schemas in a Runtime Environment

Associate additional schemas with a workspace in a runtime environment.

To associate additional schemas with a workspace in a runtime environment:

1. Start SQLcl and connect to the database where Oracle APEX is installed as SYS, for example:

   • On Windows:

     SYSTEM_DRIVE:\ sql /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password

   • On UNIX and Linux:

     $ sql /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password

2. Run the following statement:

   BEGIN
   APEX_INSTANCE_ADMIN.ADD_SCHEMA(WORKSPACE_NAME, SCHEMA_NAME, 'Y');
   END;
   /

   Where:

   • WORKSPACE_NAME is the name of the workspace.

   • SCHEMA_NAME is the name of the schema.

   • 'Y' grants privileges needed by APEX to the schema. 'N' is the default.

Removing a Schema Mapping from a Workspace in a Runtime Environment

Remove a schema mapping from a workspace in a runtime environment.

To remove a schema mapping from a workspace in a runtime environment:

1. Start SQLcl and connect to the database where Oracle APEX is installed as SYS, for example:

   • On Windows:

     SYSTEM_DRIVE:\ sql /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password

   • On UNIX and Linux:

     $ sql /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password

2. Run the following statement:

   BEGIN
   APEX_INSTANCE_ADMIN.REMOVE_SCHEMA(WORKSPACE_NAME, SCHEMA_NAME);
   END;
   /

   Where:

   • WORKSPACE_NAME is the name of the workspace.

   • SCHEMA_NAME is the name of the schema.