DBA Hub

📋Steps in this guide1/2

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).

oracle miscconfigurationintermediate
by OracleDba
20 views
1

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 role. With the workspace user in place, we can create the workspace itself using the procedure in the package. We also need to create the workspace administrator user, which we do using the procedure in the package. We can see the workspace has been created using the and views.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
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;
/

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) := '[email protected]';
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;
/

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>
2

Remove an APEX Workspace

To drop our workspace we need to remove the workspace, drop the workspace user, and remove the tablespace we created. We can see the workspace is no longer present. For more information see: - APEX_INSTANCE_ADMIN - APEX_UTIL - APEX Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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;
/

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!