Managing Existing Workspaces

About Purging Inactive Workspaces

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

• Identify inactive workspaces

• Remove the resources associated with each inactive workspace

• Delete the inactive workspaces

Identifying Inactive Workspaces

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 HTML DB activity records found in the current HTML DB 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 HTML DB engine schema for the most recent updates by each workspace.

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'));

After you identify inactive workspaces, you can purge them. Purging inactive workspaces is a two step process:

• First, remove the resources (that is, the database schemas, tablespaces, and data files) associated with each inactive workspace

• Second, drop the inactive workspaces from Oracle HTML DB

Removing the Resources Associated with Inactive Workspaces

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

To remove the resources associated with inactive workspaces:

1. Identify the schemas used by the workspaces to be deleted by joining the table containing the identified inactive workspaces to wwv_flow_company_schemas.

2. 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';
   
   

Deleting Inactive Workspaces

Once you remove the resources associated with an inactive workspace, you can delete it. You can delete inactive workspaces manually using the Oracle HTML DB 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;

Removing a Workspace

Removing 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 SQL*Plus.

To remove a workspace:

1. Log in to Oracle HTML DB Administration Services. See "Logging in to Oracle HTML DB Administration Services".

2. Click Manage Workspaces.

3. Select Remove Workspace.

4. Select a workspace name and click Next.

5. Follow the on-screen instructions.

Exporting and Importing a Workspace

To move a workspace and all associated users to a new Oracle HTML DB instance, you must export the workspace. When you export a workspace, Oracle HTML DB generates a text file. This file contains information about your workspace, all the users in your workspace, and any groups in your workspace (if applicable). You can use this file to import your workspace into another Oracle HTML DB instance.

Keep in mind, this method only imports workspace, users, and groups. This file does not contain:

• The schemas associated with this workspace, or the objects in those schemas.

• Any applications, images, cascading style sheets and static text files.

All of these items must be exported separately.

To export a workspace:

1. Log in to Oracle HTML DB Administration Services. See "Logging in to Oracle HTML DB Administration Services".

2. Click Manage Workspaces.

3. Select Export Workspace.

4. Select a workspace name and click Export Workspace.

5. To export the selected workspace, click Save File.

6. Follow the on-screen instructions.

To import a workspace:

1. Log in to Oracle HTML DB Administration Services. See "Logging in to Oracle HTML DB Administration Services".

2. Click Manage Workspaces.

3. Select Import Workspace.

4. Select a workspace name and click Next.

5. To install the workspace, click Install.

6. Follow the on-screen instructions.