Oracle Application Express (APEX) has been hosted by Oracle Academy and used for many years to facilitate the delivery of curriculum for database design and programming, SQL, PL/SQL and even APEX. To facilitate their business requirements, they had custom extensions written (in part, by our team) into the core APEX product. But this type of solution becomes difficult to maintain, because every release of APEX requires migration and rewrite of these custom extensions.
A number of months ago, I met with the Oracle Academy team to try and encourage them to move the service creation of APEX workspaces into their own custom interfaces. I told them that everything is provided to easily and programmatically create APEX workspaces, create database users (schemas), create administrators and developers within each workspace, and even pre-load APEX applications and custom database objects in each workspace, at the time of service creation.
Naturally, they asked for an example script to accomplish all of these tasks, which I authored and shared with them. Because this type of logic is very relevant for many other purposes, e.g., testing, continuous integration, DevOps, I wish to share these same annotated scripts here. If you are someone who has been using APEX for 10 years, you won't learn anything new here. But for those just getting started with APEX, I hope you find it fruitful.
Starting with an empty Oracle database, the requirements are to develop a single script which will:
- Create a tablespace (storage) for each workspace
- Create and associate multiple database users (schemas) with each workspace
- Create an APEX workspace
- Create an administrator and developer account in each workspace, restricting the access of each developer to only one of the schemas mapped to the workspace.
- Create custom database objects in each schema
And here we go...
set define '^' verify on
set concat on
set concat .
Rem
Rem Title: Demo_of_Provisioning.sql
Rem
Rem Description: This script will demonstrate use of all of the APIs and SQL statements necessary
Rem to create tablespaces, data files, database users, and APEX workspaces. From this
Rem example, it is assumed that the Academy team will be able to develop their own custom
Rem provisioning process and no longer rely upon custom extensions to the
Rem Oracle Application Express software.
Rem
Rem Notes: It is assumed that this script is run as user SYSTEM.
Rem
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jkallman 08/19/2018 - Created
column foo1 new_val LOG1
select 'Demo_of_Provisioning_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1
timing start "Create demonstration tablespaces, schemas and workspaces"
--
-- Predefine the path used for tablespace datafile creation. If you're using Oracle Managed Files
-- or grid infrastructure, then this isn't necessary and you'll remove the 'datafile' portion of
-- the CREATE TABLESPACE statements
--
define DATAFILE_PATH='/u01/app/oracle/oradata/'
--
-- Step 1: Create the Tablespaces
--
-- Since Oracle Academy will want to group multiple database users/workspaces into a single tablespace, we'll need to
-- first create the tablespaces, and then the database users in step 2
--
create tablespace OACADEMY_DATA1 datafile '^DATAFILE_PATH.iacademy_01.dbf'
size 50M autoextend on next 50M maxsize 2G extent management local
autoallocate segment space management auto;
create tablespace OACADEMY_DATA2 datafile '^DATAFILE_PATH.iacademy_02.dbf'
size 50M autoextend on next 50M maxsize 2G extent management local
autoallocate segment space management auto;
--
-- Step 2: Create the Database Users (Schemas)
--
-- It's up to you how you wish to group database users/schemas into different tablespaces.
-- You can do it by total number or free space or ratio of users to tablespaces. It's all up to you.
--
begin
-- DB users in DATA1 tablespace
execute immediate 'create user OACADEMY_DB10 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';
execute immediate 'create user OACADEMY_DB11 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';
-- DB users in DATA2 tablespace
execute immediate 'create user OACADEMY_DB20 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';
execute immediate 'create user OACADEMY_DB21 identified by "' || sys.dbms_random.string('X',30) ||
'" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';
end;
/
--
-- Step 3: Create the APEX Workspaces
--
-- Create the APEX workspaces and associate a default schema with each
--
begin
apex_instance_admin.add_workspace(
p_workspace_id => null,
p_workspace => 'OACADEMY1',
p_primary_schema => 'OACADEMY_DB10');
end;
/
begin
apex_instance_admin.add_workspace(
p_workspace_id => null,
p_workspace => 'OACADEMY2',
p_primary_schema => 'OACADEMY_DB20');
end;
/
--
-- Step 4: Add additional schemas to the existing workspaces
--
--
begin
apex_instance_admin.add_schema(
p_workspace => 'OACADEMY1',
p_schema => 'OACADEMY_DB11');
end;
/
begin
apex_instance_admin.add_schema(
p_workspace => 'OACADEMY2',
p_schema => 'OACADEMY_DB21');
end;
/
--
-- Show a quick summary of the workspaces and schemas
--
column workspace_name format a50
column schema format a40
select workspace_name, schema from apex_workspace_schemas;
--
-- Step 5: Create an administrator account and a developer account in each worskpace
--
--
begin
-- We must set the APEX workspace security group ID in our session before we can call create_user
apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY1'));
apex_util.create_user(
p_user_name => 'BOB',
p_email_address => 'bob@bob.com',
p_default_schema => 'OACADEMY_DB10',
p_allow_access_to_schemas => 'OACADEMY_DB10',
p_web_password => 'change_me',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- workspace administrator
apex_util.create_user(
p_user_name => 'JUNE',
p_email_address => 'june@june.com',
p_default_schema => 'OACADEMY_DB11',
p_allow_access_to_schemas => 'OACADEMY_DB11',
p_web_password => 'change_me',
p_developer_privs => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- developer
commit;
end;
/
begin
-- We must set the APEX workspace security group ID in our session before we can call create_user
apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY2'));
apex_util.create_user(
p_user_name => 'ALICE',
p_email_address => 'alice@alice.com',
p_default_schema => 'OACADEMY_DB20',
p_allow_access_to_schemas => 'OACADEMY_DB20',
p_web_password => 'change_me',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- workspace administrator
apex_util.create_user(
p_user_name => 'AUGUST',
p_email_address => 'august@august.com',
p_default_schema => 'OACADEMY_DB21',
p_allow_access_to_schemas => 'OACADEMY_DB21',
p_web_password => 'change_me',
p_developer_privs => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' ); -- developer
commit;
end;
/
--
-- Show a quick summary of the APEX users
--
column workspace_name format a30
column user_name format a20
column email format a25
column is_admin format a10
column is_application_developer format a10
select workspace_name, user_name, email, is_admin, is_application_developer from apex_workspace_apex_users order by 1,2;
--
-- Install custom database objects in each schema by simply running one more SQL scripts. Note that the
-- script below is something that you author and maintain. It will do all DML and DDL you have in it,
-- and we simply iterate through the schemas and run the script each time.
--
alter session set current_schema = OACADEMY_DB10;
@custom.sql
alter session set current_schema = OACADEMY_DB11;
@custom.sql
alter session set current_schema = OACADEMY_DB20;
@custom.sql
alter session set current_schema = OACADEMY_DB21;
@custom.sql
timing stop
spool off
And to complete the lifecycle, I also authored a simple SQL script which will cleanup everything created above - removing the APEX workspaces, database users and tablespaces.
set define '^' verify on
set concat on
set concat .
Rem
Rem Title: Demo_of_Cleanup.sql
Rem
Rem Description: This script will cleanup all objects created by script Demo_of_Provisioning.sql
Rem
Rem Notes: It is assumed that this script is run as user SYSTEM.
Rem
Rem **** THIS SCRIPT IS DESTRUCTIVE **** - It will drop tablespaces, data files, workspaces, schemas, etc.
Rem
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jkallman 08/19/2018 - Created
column foo1 new_val LOG1
select 'Demo_of_Cleanup_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1
timing start "Remove all workspaces, schemas and tablespaces"
--
-- Step 1: Remove the APEX Workspaces
--
--
begin
apex_instance_admin.remove_workspace(
p_workspace => 'OACADEMY1' );
end;
/
begin
apex_instance_admin.remove_workspace(
p_workspace => 'OACADEMY2' );
end;
/
--
-- Step 2: Drop the database users
--
--
drop user OACADEMY_DB10 cascade;
drop user OACADEMY_DB11 cascade;
drop user OACADEMY_DB20 cascade;
drop user OACADEMY_DB21 cascade;
--
-- Step 3: Drop the tablespaces
--
--
drop tablespace oacademy_data1 including contents and datafiles;
drop tablespace oacademy_data2 including contents and datafiles;
timing stop
spool off
