8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
APEX Tips : Create and Remove APEX Workspace Using the Command Line
This article demonstrates how to create and remove APEX workspaces using the command line (SQL*Plus or SQLcl).
Related articles.
Create an APEX Workspace
We will create a new tablespace and user to act as our workspace user. The user needs a specific set of privileges, which we can source from the APEX_GRANTS_FOR_NEW_USERS_ROLE role.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
declare
l_ts_name varchar2(30) := 'test_ws_ts';
l_db_username varchar2(30) := 'test_ws';
l_db_password varchar2(30) := 'test_ws_passwd';
begin
-- Create tablespace.
execute immediate 'create tablespace ' || l_ts_name || ' datafile size 100m autoextend on next 100m';
-- Create user.
execute immediate 'create user ' || l_db_username || ' identified by ' || l_db_password ||
' default tablespace ' || l_ts_name || ' quota unlimited on ' || l_ts_name;
-- Grant privileges.
for cur_rec in (select privilege
from sys.dba_sys_privs
where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE')
loop
execute immediate 'grant ' || cur_rec.privilege || ' to ' || l_d_username;
end loop;
end;
/
With the workspace user in place, we can create the workspace itself using the ADD_WORKSPACE procedure in the APEX_INSTANCE_ADMIN package. We also need to create the workspace administrator user, which we do using the CREATE_USER procedure in the APEX_UTIL package.
declare
l_workspace varchar2(30) := 'TEST_WS';
l_db_username varchar2(30) := 'TEST_WS';
l_admin_username varchar2(30) := 'admin';
l_admin_password varchar2(30) := 'adminPasswd123';
l_admin_email varchar2(30) := 'admin@example.com';
begin
apex_instance_admin.add_workspace(
p_workspace => l_workspace,
p_primary_schema => l_db_username);
apex_util.set_workspace(
p_workspace => l_workspace);
apex_util.create_user(
p_user_name => l_admin_username,
p_web_password => l_admin_password,
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_email_address => l_admin_email,
p_default_schema => l_db_username,
p_change_password_on_first_use => 'Y' );
commit;
end;
/
We can see the workspace has been created using the APEX_WORKSPACES and APEX_WORKSPACE_APEX_USERS views.
column workspace_name format a30
column schema format a30
select workspace_name, schema
from apex_workspace_schemas
where workspace_name = 'TEST_WS'
order by 1;
WORKSPACE_NAME SCHEMA
------------------------------ ------------------------------
TEST_WS TEST_WS
SQL>
column workspace_name format a30
column user_name format a30
select workspace_name,
user_name
from apex_workspace_apex_users
where workspace_name = 'TEST_WS'
order by 1, 2;
WORKSPACE_NAME USER_NAME
------------------------------ ------------------------------
TEST_WS ADMIN
SQL>
Remove an APEX Workspace
To drop our workspace we need to remove the workspace, drop the workspace user, and remove the tablespace we created.
declare l_workspace varchar2(30) := 'TEST_WS'; l_db_username varchar2(30) := 'TEST_WS'; l_ts_name varchar2(30) := 'test_ws_ts'; begin apex_instance_admin.remove_workspace(l_workspace); execute immediate 'drop user ' || l_db_username || ' cascade'; execute immediate 'drop tablespace ' || l_ts_name || ' including contents and datafiles'; end; /
We can see the workspace is no longer present.
column workspace_name format a30
column schema format a30
select workspace_name, schema
from apex_workspace_schemas
where workspace_name = 'TEST_WS'
order by 1;
no rows selected
SQL>
column workspace_name format a30
column user_name format a30
select workspace_name,
user_name
from apex_workspace_apex_users
where workspace_name = 'TEST_WS'
order by 1, 2;
no rows selected
SQL>
For more information see:
Hope this helps. Regards Tim...