Deleting a Workspace

3.12.4 Deleting a Workspace

Deleting a workspace does not remove any of the associated database objects. To remove the associated schemas, a database administrator (DBA) must use a standard database administration tool, such as Oracle Enterprise Manager or SQLcl.

Identifying and Deleting Inactive Workspaces
Learn how to identify and delete inactive workspaces.
Deleting Workspaces in a Full Development Environment
Delete workspaces in a full development environment.
Deleting Workspaces in a Runtime Development Environment
Delete workspaces in a runtime development environment.

3.12.4.1 Identifying and Deleting Inactive Workspaces

Learn how to identify and delete inactive workspaces.

Parent topic: Deleting a Workspace

3.12.4.1.1 How to Identify Inactive Workspaces

If you are managing a large hosted Oracle APEX instance, manually deleting inactive workspaces can free up resources for other users. The process of manually deleting inactive workspaces consists of the following steps:

Step 1: Identify inactive workspaces. See Identifying Inactive Workspaces By Checking Recent Updates.
Step 2: Remove the resources (that is, the database schemas, tablespaces, and data files) associated with each inactive workspace. See Removing the Resources Associated with Inactive Workspaces.
Step 3: Drop the inactive workspaces from APEX. See Deleting Inactive Workspaces.

Tip:

To systematically purge inactive workspaces, you must configure Workspace Purge settings. See Purging Inactive Workspaces.

Parent topic: Identifying and Deleting Inactive Workspaces

3.12.4.1.2 Identifying Inactive Workspaces By Checking Recent Updates

The first step in determining if a workspace is inactive is to establish some basic rules. A common approach is to base the rules on the Oracle APEX activity records found in the current APEX engine schema.

The following DDL (data definition language) creates a table of all workspaces requested before June 28, 2004 but that have been inactive since June 10, 2004. In this example, inactivity is determined by checking a key within the APEX engine schema for the most recent updates by each workspace.

ALTER SESSION SET CURRENT_SCHEMA = APEX_240100;
CREATE TABLE ws_to_purge AS
 SELECT c.security_group_id, c.company_name, c.admin_email, c.request_date,
 SYSDATE last_updated_on, 'Y' ok_to_delete
   FROM wwv_flow_provision_company c
  WHERE
c.request_date <= to_date('20040628','YYYYMMDD') AND
     (  not exists
 (SELECT NULL /* Activity Log */
        FROM wwv_flow_activity_log l
       WHERE l.security_group_id = c.security_group_id
         AND l.time_stamp > to_date('20040610','YYYYMMDD'))
 )
    AND NOT EXISTS
     (SELECT NULL /* workspace applications */
        FROM wwv_flows f
       WHERE f.security_group_id = c.security_group_id
         AND f.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Pages */
        FROM wwv_flow_steps s
       WHERE s.security_group_id = c.security_group_id
         AND s.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Regions */
        FROM wwv_flow_page_plugs p
       WHERE p.security_group_id = c.security_group_id
         AND p.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Items */
        FROM wwv_flow_step_items i
       WHERE i.security_group_id = c.security_group_id
         AND i.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Templates */
        FROM wwv_flow_templates t
       WHERE t.security_group_id = c.security_group_id
         AND t.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Files uploaded */
        FROM wwv_flow_file_objects$ o
       WHERE o.security_group_id = c.security_group_id
         AND o.created_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* SQL Workshop history */
        FROM wwv_flow_sw_sql_cmds s
       WHERE s.security_group_id = c.security_group_id
         AND s.created_on > to_date('20040610','YYYYMMDD'));

3.12.4.1.3 Removing the Resources Associated with Inactive Workspaces

After you have identified inactive workspaces in a single table, the next step is to remove them.

Note:

Before removing the schemas, tablespaces, or data files associated with inactive workspaces, make sure these resources are not being used in any other workspace or application.

To remove the resources associated with inactive workspaces:

Identify the schemas used by the workspaces to be deleted by joining the table containing the identified inactive workspaces to wwv_flow_company_schemas.
Drop the schemas, tablespaces, and data files used exclusively by the inactive workspaces from the database. You can identify the schemas to drop by running a query similar to the following:
```
SELECT s.schema
  FROM ws_to_purge ws,
       wwv_flow_company_schemas s
WHERE s.security_group_id = ws.security_group_id
   AND ws.ok_to_delete = 'Y';
```

Parent topic: Identifying and Deleting Inactive Workspaces

3.12.4.1.4 Deleting Inactive Workspaces

Once you remove the resources associated with an inactive workspace, you can delete the workspace. You can delete inactive workspaces manually using the Administration Services application. Or, you can delete them programmatically as shown in the following PL/SQL example.

BEGIN 
     FOR c1 IN (SELECT security_group_id  
                 FROM ws_to_purge
                 WHERE ok_to_delete = 'Y')
     LOOP
         WWV_FLOW_PROVISIONING.TERMINATE_SERVICE_BY_SGID(c1.security_group_id);
     END LOOP;
 END;

Parent topic: Identifying and Deleting Inactive Workspaces

3.12.4.2 Deleting Workspaces in a Full Development Environment

Delete workspaces in a full development environment.

To delete a workspace:

Sign in to Oracle APEX Administration Services.
Click Manage Workspaces.
From Workspace Actions:
1. Under Workspace Actions, click Remove Workspace.
  
  The Remove Workspace page appears.
2. Workspace - Select the workspace to be removed and click Next.
3. On the Remove Workspace page:
  - Review the workspace name and the number of applications and users.
  - Select Check to confirm that you wish to proceed with the removal.
  - Click Next.
4. Confirm the displayed information and click Remove Workspace.
From the Existing Workspaces page:
1. Under Workspace Reports, click Existing Workspaces.
  
  The Existing Workspaces page appears.
2. Under the Action column, click Delete.
  
  The Remove Workspace page appears.
3. On the Remove Workspace page:
  - Review the workspace name and the number of applications and users.
  - Select Check to confirm that you wish to proceed with the removal.
  - Click Next.
4. Confirm the displayed information and click Remove Workspace.

Parent topic: Deleting a Workspace

3.12.4.3 Deleting Workspaces in a Runtime Development Environment

Delete workspaces in a runtime development environment.

To delete a workspace in a runtime development environment:

Start SQLcl and connect to the database where Oracle APEX is installed as SYS, for example:

On Windows:

SYSTEM_DRIVE:\ sql /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sql /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

Run the following statement:

ALTER SESSION SET CURRENT_SCHEMA = APEX_240100

Run the following statement:
```
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(WORKSPACE_NAME, DROP_USER, DROP_TABLESPACE)
END;
```
Where:
- WORKSPACE_NAME is the name of the workspace.
- DROP_USER is either Y or N. The default is N.
- DROP_TABLESPACE is either Y or N. The default is N.

Parent topic: Deleting a Workspace