They key thing is, not everything needs to be point and click - that feels a little important.
I like to frequently blow away & re-create a bunch of users, workspaces & applications in one foul swoop. I'd like to share a little of what I've done since it was a topic of discussion recently at the Perth APEXposed.
To use the APIs below, your schema will require the APEX_ADMINISTRATOR_ROLE, described by Martin here.
To create & define all the training accounts, I use the following procedure in a package I compile in my parsing schema.
Privileges are a little different when encapsulated in a package, so direct EXECUTE access on APEX_INSTANCE_ADMIN would be required to avoid the infamous PLS-00201. Permissions such as CREATE USER would also be required.
PROCEDURE define_users (p_nbr_users NUMBER) IS lc_user VARCHAR2(10); BEGIN << define_users >> FOR i IN 1..p_nbr_users LOOP lc_user := 'train'||i; << drop_user >> DECLARE e_no_user exception; pragma exception_init(e_no_user, -1918); BEGIN EXECUTE IMMEDIATE 'DROP USER '||lc_user||' CASCADE'; EXCEPTION WHEN e_no_user THEN NULL; END drop_user; EXECUTE IMMEDIATE 'CREATE USER '||lc_user||' IDENTIFIED BY '||lc_user; EXECUTE IMMEDIATE 'ALTER USER '||lc_user||' QUOTA 100M ON users'; EXECUTE IMMEDIATE 'GRANT CONNECT TO '||lc_user ; EXECUTE IMMEDIATE 'GRANT CREATE SYNONYM TO '||lc_user; EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO '||lc_user; EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO '||lc_user; EXECUTE IMMEDIATE 'GRANT CREATE TYPE TO '||lc_user; EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO '||lc_user; EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO '||lc_user; EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO '||lc_user; END LOOP define_users; END define_users;All my other procedures just encapsulate the following steps and example API calls. I highly recommend specifying formal parameter names as the signature to these APIs often between APEX versions.
My procedure to define my workspace makes calls
APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(lc_user,'N','N');Create the workspace
APEX_INSTANCE_ADMIN.ADD_WORKSPACE (p_workspace_id => ln_workspace_id ,p_workspace => lc_user ,p_primary_schema => lc_user ,p_additional_schemas => '')A new call required for 4.1.1, found by Dimitri, announced by Patrick
apex_instance_admin.enable_workspace;Set the context regarding which workspace
apex_util.set_security_group_id(p_security_group_id => ln_workspace_id);Create a number of users, with various developer privileges.
APEX_UTIL.CREATE_USER( p_user_name => 'ADMIN' ,p_web_password => /* my attempt to keep generic accounts passwords unmentionable */ ,p_email_address => 'firstname.lastname@example.org' ,p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ,p_default_schema => lc_user ,p_allow_access_to_schemas => lc_user ,p_change_password_on_first_use => 'N');Then before installing an application, you need to set the workpace and schema
APEX_APPLICATION_INSTALL.SET_WORKSPACE_ID(p_workspace_id); APEX_APPLICATION_INSTALL.SET_SCHEMA(p_schema);Then you can either nominate the application id or have one generated
APEX_APPLICATION_INSTALL.SET_APPLICATION_ID(p_app_id); -- or APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;You may also choose to set such properties as application name and alias
APEX_APPLICATION_INSTALL.SET_APPLICATION_NAME (p_app_name); APEX_APPLICATION_INSTALL.SET_APPLICATION_ALIAS(p_app_alias);Then you generate an offset for all the IDs in the export script
APEX_APPLICATION_INSTALL.GENERATE_OFFSET;If you're creating multiple applications for one workspace, all you need each time is
To execute these APIs among calls to import previously exported applications in SQL*Plus (wasn't that a mouthful), I used
set define '^' accept my_app DEFAULT 'C:\my_everything\f_123_example.sql' -- set app context exec apex_application_install.generate_application_id; exec apex_application_install.generate_offset; -- install app from export @^my_app -- rinse and repeatAnd that's essentially how I do it, just bundled up to suit my needs. It'll be pretty easy for you to grab what you need and sort out your own scripts.