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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4eSjx9-12P0-fG11qNbqIN7gZYjW2jvGlkWVfQoN079jlZhSa63Ijq6es96kGZcGtXSkjpjqamKa5hLmUlpKpOTwQ0jQIhqX_UMu5vz9AttFsBaF6P9rBvebONdDGlReqSAfZ89TLi9fm/s800/apex_manage_workspaces.png)
Next, name the workspace "TEST" and select Next.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQVcu65u0WAriCyhVEKyCO-ID3Xrx93uA5W53VM16ZG6MjaBF7jOlbuRTKMBRQiANLu0Ez41bkC2XZ-fPHkgnX_uQ3VadPoo-6iz2PgxLciWaSCTy6jwv6eLGhS064jbDBWA-jWJVqMU9E/s800/02_create_workspace.png)
Select Yes from the drop down for "Re-use existing schema?" I picked APEX_TEST as the schema to map to. Then select Next.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_LEXM8Yt1YvJ_we5xB88IhUFyPvr2YOjcqG7T4cE9yHzZAqYJUP5n5ffiT7UuAVv9JuK4WX8Cbf1JKXruUpZiRWluh54ugLgEEaOynzUXYAAOQWMjhF8kuOJhDz5Knvp5rwhnP8AAVmtQ/s800/03_create_workspace.png)
I left the default Administrator Username, ADMIN. The password is ADMIN and the email is ADMIN@EMAIL.COM. Select Next.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4G1UxJiubkCz7qOdm_6ktIJEsm9OfqIfxUfuCJ5ewOWNFJ74e8n3eHReQb7BrdaJ-SQR8NXZaKUa-yuXz1W_Bgxko3r1UPL0zFtgsN3IYZg-N-3QDS3xovhaMIiFTbRAOgKWQT9nctX4/s800/04_create_workspace.png)
I'm then prompted to confirm the details of the new workspace. Select Create.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKkUB89ma-xPY1YqMDzwRVFUtFvHU2EscROrj5u5mLaY1KrbwjhRRrPuuT99WGi_gBm0el5azy09pwbxq2aB6f6KE2jAXl0hwVFtxENpL2jmO65KQhu9mSqwxfhqhw2ITL5mppwzvt3cjO/s800/06_create_workspace.png)
My workspace has been created.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiT34Y_ygfVC_FzBNxE5o9w5YUMjKjoGVwh-goOPvR-tS86XRjLbcetA6D3_bvkpspXjlDGnL8jMu7-hOLaRr4ocQcitO9vNXvvCF25sjEA2XDd7onO2tnPDrfZpKw5Nns85ztTFXgnGL1I/s800/07_create_workspace.png)
You'll be redirected back to the Manage Workspace page.
Now select the link for Manage Workspace to Schema Assignments
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0H0A9LRycgxElfN-fOkvsFz0EwyvF9KpUib_yiNnf78GDQOw1T3SCl_xb1AiOe4_2mLmiYlDVTqPTnFs4rtWuYNmzgZWHH1IZd6BdZ3WDji2MCu-qj562kJ4v0XNqNtXLljZt4BOEFF_i/s800/apex_manage_workspaces.png)
You'll be taken to a page that looks like this, select Create
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmqNjnmQWWwbR4J7Fary4nWjRfMLSBtbS-wz-I0nlfas3Ef6UwXuslp8X7NN1c4Y5inuR0ZmnYVD_Wqk68bZzgE-qBIhdOxb4auEnov4O5hcSTGWUFgy3K15gL51KUukQ5PdprRsaGOiGq/s800/08_create_workspace.png)
The check "Existing" when prompted for a New or Existing Schema
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWs9CyR9jUiNYEHHKaf3dUuIilRTyTH4dEWNfuYcKfcrFeyluo1FY-x-U7jtHuRg-dIk40C9vKJFPFPtUJk-LMMksgiUMp637xLT92escGMdRIqfKIACkLxru18ukP3ElCFp8nCL0krOUF/s800/09_create_workspace.png)
Select your newly created workspace, TEST, and click on Next
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigYXw38o9bkS87pnf5t0SIp9E9YTNNqbDkrl77FbO0uSDtoGShiuC2JK8nIha635Pp0o6s2Hkny8A4Si0-xmu7l_OOim-_KZY_HJ6iNRcwgviMJIUSySibaOfnzuu0qmskaHu85U9AoEMK/s800/10_create_workspace.png)
Either enter the schema you want to map to or select it from the popup, click Next
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg50YgZ6qQK51ml-3UsMGw-VkFtZ-eoAIBDZfO94fhvzVciqOFIbIVLodRbGPdifr9QnQNMRA-nsIownn4WncdPNWYZXPihF9gnfkV6z1VOZVivOox8SVWki8xD3n8g5Az6pLnI4iJXLvU3/s800/12_create_workspace.png)
Confirm your settings and click on Add Schema
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhC6btMD6N3np1BUxQ_4029Uqwpb_-yGKQleeoYUBG9Qk39E8iHYTm1gRQgFR8xmeFINzO-cde6grCrn9uYlQl8Goh4IX16NCbmMaWJJ2ae1Mxi8rqma8URiRsXmlxfYv50NVnrOZPrVzD/s800/13_create_workspace.png)
And voila! You've now mapped your workspace to 2 separate schemas
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgl8GZblTreC9Jhoj2XbyfovowgVnZB72KANFnMJQDo2rFQFtk_djdNdYtpH5crdmYsKA6s-QB9w6UmOjvtK4YKBGUeWzO3Ww0D0YzM2HCeYGo1CJeqb4faQWNI5QifZz5NQK4jtGWvJXvb/s800/14_create_workspaces.png)
Manually
You can also do this if you create a workspace via APEX_INSTANCE_ADMIN,
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:
BEGINAnd you're done
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;
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtQcNEzXuyCnGkdKjYu_AMu4vR6LpN-nIpB30Hsk1891k4YFxKkNZ8Unaplz2t1v1CICslTUy6PWVaaLHvZlEPrTcZI1cys_8lUxnI1yxuDO-OpUxAGpGeyh7SDrlpboeFvQwZszdW1ao7/s800/14_create_workspace.png)
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;
4 comments:
Thank you for the information :-)
Thanks for the info, Mate
Hi, the post is OK, i'm doing some tests. Question is, i'm trying to create an IR with query builder, but i can only see ONE schema in the create window. Cannot see both mapped schemas.
thanks in advance
didn't know about the apex_instance_admin package ... thanks buddy `:)
Post a Comment