APEX: Assign Multiple Schemas To A Single Workspace Home --> Administrative Services --> Manage Workspaces --> Assign Multiple Schemas To A Single Workspace

While "teaching" APEX to a group of folks I was asked how to assign multiple schemas to a single workspace. For the life of me, I couldn't remember or figure out how to do it through the web interface. Strangely, I had recently been playing with the APEX_INSTANCE_ADMIN package and I knew it was possible...just couldn't find the right way through the interface.

We'll start with creating a new workspace using the web interface.

First, click on Create Workspace



Next, name the workspace "TEST" and select Next.



Select Yes from the drop down for "Re-use existing schema?" I picked APEX_TEST as the schema to map to. Then select Next.



I left the default Administrator Username, ADMIN. The password is ADMIN and the email is ADMIN@EMAIL.COM. Select Next.



I'm then prompted to confirm the details of the new workspace. Select Create.



My workspace has been created.



You'll be redirected back to the Manage Workspace page.

Now select the link for Manage Workspace to Schema Assignments



You'll be taken to a page that looks like this, select Create



The check "Existing" when prompted for a New or Existing Schema



Select your newly created workspace, TEST, and click on Next



Either enter the schema you want to map to or select it from the popup, click Next



Confirm your settings and click on Add Schema



And voila! You've now mapped your workspace to 2 separate schemas



Manually
You can also do this if you create a workspace via APEX_INSTANCE_ADMIN, but only on creation, there seems to be no facility in that package to add it after creation. The procedure call is ADD_WORKSPACE.

I'll create a new workspace, MANUAL_WORKSPACE and assign the primary schema as APEX_TEST and the secondary (you can add as many as you want with a colon delimited list) will be APEX_USER:

BEGIN
  apex_instance_admin.add_workspace
    ( p_workspace_id => NULL,
      p_workspace => 'TEST_MANUAL',
      p_primary_schema => 'APEX_TEST',
      p_additional_schemas => 'APEX_USER' );
END;
/

PL/SQL procedure successfully completed.

COMMIT;

And you're done



Update 12/05/2009 10:17 PM
I was wrong, you can add a schema after it has been created using the APEX_INSTANCE_ADMIN package...If you want to add a second schema to an already existing workspace, use the ADD_SCHEMA procedure.

CJUSTICE@TESTING>EXEC apex_instance_admin.add_schema( 'TEST', 'APEX_USER' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
CJUSTICE@TESTING>COMMIT;

Labels: apex, apex_instance_admin, howto

  ¶ 12/05/2009 03:05:00 AM