Managing Existing Workspaces

This section describes how Instance administrators can manage existing workspaces within an Oracle Application Express instance.

Topics:

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

Topics:

Deleting Workspaces in a Full Development Environment

To delete a workspace in a full development environment:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Reports, click Existing Workspaces.
Under the Action column, click Delete.
Follow the on-screen instructions.

Deleting Workspaces in a Runtime Development Environment

To delete a workspace in a runtime development environment:

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:

On Windows:

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

On UNIX and Linux:

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

Run the following statement:

ALTER SESSION SET CURRENT_SCHEMA = APEX_040200

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.

About Deleting Inactive Workspaces

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

Identify inactive workspaces.
Remove the resources associated with each inactive workspace.
Delete the inactive workspaces.

Tip:

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

Topics:

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

ALTER SESSION SET CURRENT_SCHEMA = APEX_040200;
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 Application Express.

Viewing Workspaces with Only a Sample Application

Another way to identify potentially inactive workspaces is to view the Workspaces with Only Sample Application report.

To view a report or workspaces only containing only a sample application.

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Reports, click Workspaces with Only Sample Application.

The Workspaces with Only Sample Applications page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.

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

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 Oracle Application Express 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;

Locking a Workspace

Instance administrators can lock a workspace to address security or performance issues. Locking a workspace immediately locks all workspace administrator, developer and user accounts in the workspace. It also changes the status of all applications in the workspace to Unavailable.

Warning:

Locking a workspace makes it permanently inaccessible.

To lock a workspace:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Actions, click Lock Workspace.
For Workspace, select the workspace you want to lock and click Next.
Review the information about applications and users and click Lock Workspace.

Managing Workspace to Schema Assignments

When users log in to Oracle Application Express, they log in to a shared work area called a workspace. Each workspace can have multiple associated (or mapped) schemas. By associating a workspace with a schema, developers in that workspace can:

Build applications that interact with the database objects in that schema.
Create new database objects in that schema.

Topics:

Viewing Schema and Workspace Assignments in a Development Environment

Instance administrators can view the existing schema to workspace assignment on the Manage Workspace to Schema Assignments page.

To view the existing schema to workspace assignment:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Actions, click Manage Workspace to Schema Assignments.

The Manage Workspace to Schema Assignments page appears and lists all workspaces in your environment along with their associated schemas.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.

Viewing Schema and Workspace Assignments in a Runtime Environment

To view the existing schema to workspace assignment in a runtime environment:

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:

On Windows:

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

On UNIX and Linux:

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

Run the following statement:

ALTER SESSION SET CURRENT_SCHEMA = APEX_040200

Run the following statement:
```
SELECT APEX_INSTANCE_ADMIN.GET_SCHEMAS(WORKSPACE_NAME) 
FROM DUAL;
```
Where WORKSPACE_NAME is the name of the workspace.

Editing Existing Schema and Workspace Assignments

To edit an existing schema and workspace assignment:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Manage Workspaces, click Manage Workspace to Schema Assignments.

The Manage Workspace to Schema Assignments page appears.
To edit an existing workspace to schema assignment:
1. Select the workspace name.
  
  The Edit Schema to Workspace Assignment page appears.
2. Select a new workspace or schema.
3. Click Apply Changes.

Associating Additional Schemas with a Workspace

Instance administrators can associate (or map) additional existing schemas to a workspace.

Associating Additional Schemas in a Full Development Environment

To associate additional schemas with a workspace:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Actions, click Manage Workspace to Schema Assignments.

The Manage Workspace to Schema Assignments page appears.
Click Add Schema.

The Add Schema wizard appears.
For New or Existing Schema, select Existing and click Next.
Follow the on-screen instructions to associate the existing schema to a workspace.
To verify that the new schema is added to the workspace:
1. Log in to the workspace on Oracle Application Express.
2. Review the Workspace Schemas list on the Workspace home page. The list shows all schemas currently associated with this workspace.

Associating Additional Schemas in a Runtime Environment

To associate additional schemas with a workspace in a runtime environment:

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:

On Windows:

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

On UNIX and Linux:

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

Run the following statement:

ALTER SESSION SET CURRENT_SCHEMA = APEX_040200

Run the following statement:
```
BEGIN
APEX_INSTANCE_ADMIN.ADD_SCHEMA(WORKSPACE_NAME, SCHEMA_NAME)
END;
```
Where:
- WORKSPACE_NAME is the name of the workspace.
- SCHEMA_NAME is the name of the schema.

Removing a Schema Mapping from a Workspace in a Runtime Environment

To remove a schema mapping from a workspace in a runtime environment:

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:

On Windows:

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

On UNIX and Linux:

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

Run the following statement:

ALTER SESSION SET CURRENT_SCHEMA = APEX_040200

Run the following statement:
```
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_SCHEMA(WORKSPACE_NAME, SCHEMA_NAME)
END;
```
Where:
- WORKSPACE_NAME is the name of the workspace.
- SCHEMA_NAME is the name of the schema.

Creating a New Schema

Instance administrators can create a new schema and associate it with a workspace.

To create a new schema for a workspace:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Actions, click Manage Workspace to Schema Assignments.

The Manage Workspace to Schema Assignments page appears.
Click Add Schema.

The Add Schema wizard appears.
For New or Existing Schema, select New and click Next.
For Choose Workspace, select the workspace to associate the new schema with and click Next.
For Identify Schema:
1. Schema - Enter a unique name containing only letters.
  
  Tip:
  
  To verify that the new schema name is unique, open the select list and search for the name.
2. Password - Enter a case-sensitive password.
3. Default Tablespace - Identify the default tablespace that you want this schema to use.
4. Temporary Tablespace - Identify the temporary tablespace you want this schema to use.
5. Click Next.
Confirm the information and click Add Schema.
To verify that the new schema is added to the workspace:
1. Log in to the workspace on Oracle Application Express.
2. Review the Workspace Schemas list on the Workspace home page. The list shows all schemas associated with this workspace.

Managing Component Availability

Instance administrators can control which components are available within an Oracle Application Express instance. Configurable components include: Websheets, SQL Workshop, Application Builder, and Team Development.

To configure component availability:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Actions, click Manage Component Availability.

The Component Availability page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.
Locate the workspace for which you want to enable or disable components.
Click the Edit icon adjacent to the workspace name.

The Workspace Settings page appears.
Under Workspace Settings, select Yes to enable a component or No to disable it. Options include:
- Enable Application Builder
- Enable SQL Workshop
- Enable PL/SQL Editing in SQL Workshop
- Enable Team Development
Click Apply Changes.

Defining a Workspace Message

You can display a message on the workspace home page by defining a Workspace Message.

To define a workspace message:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Actions, click Manage Component Availability.

The Component Availability page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.
Locate the workspace for which you want to define a workspace message.
Under Workspace Message, enter a message in the Message field. To format the display, include standard HTML tags.
Click Apply Changes.

Performing a Workspace Search

To perform a workspace search:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Under Workspace Tasks, click Find a Workspace.

The Workspace Search page appears

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.
To view workspace details, click the workspace name. See "About the Workspace Summary Report".

Viewing Existing Workspaces

Use the Existing Workspaces report to view an interactive report of existing workspaces. From the Existing Workspaces report, you can view or edit workspace details.

To access the Existing Workspaces report:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Reports, click Existing Workspaces.

The Existing Workspaces page appears

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.
To edit workspace information, click the Edit icon adjacent to the workspace name. See "Editing Workspace Details"
To view workspace details, click the workspace name. The Workspace Summary Report. See "About the Workspace Summary Report".

Editing Workspace Details

To edit workspace details:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Reports, click Existing Workspaces.

The Existing Workspaces report appears.
To edit workspace details, click the Edit icon adjacent to the workspace name.

The Edit Workspace Information page appears.
Edit the appropriate attributes.

To learn more about a specific attribute, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark.
Click Apply Changes.

Viewing Workspace Details

Instance administrators can view workspace information by viewing the Workspace Summary Report.

To view workspace details:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Reports, click Workspace Details.

The Workspace Details page appears.
Make a selection from the Workspace list at the top of the page and click Set.

The Workspace Summary Report page appears.

Description of the illustration wrkspc_details.gif

The following buttons appear above the report:
- Remove - Click Remove to remove the current workspace.
- Edit - Click Edit to edit workspace details.
- Schema Assignments - Click Schema Assignments to manage workspace schema assignments.
- Manage Users - Click Manage Users to edit or create new users.
- Email - Click Email send this report by email.

About the Workspace Summary Report

The Workspace Summary Report is divided into the following major sections:

Workspace. Displays high-level information about selected workspace.
Workspace Schemas. Displays the schema name, and default tablespace, privileges granted, and database role privileges.

When users log in to Oracle Application Express, they log in to a shared work area called a workspace. Each workspace can have multiple associated schemas. By associating a workspace with a schema, developers can build applications that interact with the objects in that schema and create new database objects in that schema. To edit workspace to schema assignments, click Workspace to Schema Assignments. See "Managing Schemas".
Database Applications. Lists details about the database applications within the workspace.
Workspace Websheets. Lists details about the websheets within the workspace.
Packaged Applications. Lists packaged application within the workspace.
SQL Workshop. Lists the number of SQL scripts, SQL script results, and SQL Command history.
Team Development. Lists the number of features, milestones, bugs, To Dos, feedback entries, news entries, and links.
Workspace Application Activity Summary. Details statistics about workspace applications.

Other sections include:

5 Most Active Applications
Developer Activity Summary
Database Object Counts by Object Type
5 Top Tables by Row Count, Reported by Schema

Viewing Workspace Database Privileges

Instance administrators can view a summary of workspace database privileges on the Workspace Database Privileges page.

To view workspace database privileges:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Reports, click Workspace Database Privileges.

The Workspace Database Privileges page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.
To view workspace details, click the workspace name.

The Workspace Summary report appears. See "About the Workspace Summary Report".

Viewing File Size and File Count by Workspace

To view total file size and file count by workspace:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Workspace Reports, click Manage File Utilization.

The File Utilization by Workspace page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.

Exporting and Importing a Workspace

To move a workspace and all associated users to a new Oracle Application Express instance, an Instance administrator must export the workspace. When you export a workspace, Oracle Application Express 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 Application Express instance.

Exporting a workspace and importing it into a new database instance enables developers to import application components. Therefore, to provide maximum capabilities Oracle recommends that when building new environments (for example, for production or testing) create these workspaces by importing the development workspaces.

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.

These items must be exported separately.

See Also:

"Deploying an Application" in Oracle Application Express Application Builder User's Guide

Topics:

Exporting a Workspace

To export a workspace:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Export Import, click Export Workspace.
Select a workspace and then click Export Workspace.
To export the selected workspace, click Save File.
Follow the on-screen instructions.

Importing a Workspace

To import a workspace:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Export Import, click Import Workspace.
Click Browse, select a workspace export file, and click Next.
To install the workspace, click Install.
Follow the on-screen instructions.

Viewing Application Attributes

Instance administrators can view applications by workspace on the Application Attributes page.

To view the Application Attributes page:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Manage Applications, click Application Attributes.

The Application Attributes page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.

Changing Application Build Status Set During Deployment

Every Oracle Application Express application has an application-level attribute called Build Status. You can use this attribute to prevent an application from being modified by other developers. Build Status has two settings:

Run and Build Application - Developers can both run and edit an application.
Run Application Only - Developers can only run an application.

Setting the Build Status to Run Application Only is an effective way to prevent other developers from modifying it.

You can change the Build Status of an application as follows:

Developers and administrators can change the Build Status attribute on the Edit Application page. See "Availability" in Oracle Application Express Application Builder User's Guide.
Workspace administrators can change the Build Status of applications within the current workspace on the Manage Application Build Status page. See "Changing Build Status for Multiple Applications".
Instance administrators can change the Build Status of an application on the Build Status page as described in the next section.

To change a Build Status as an Instance administrator:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Manage Applications, click Build Status.

The Build Status page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.
Click the Edit icon adjacent to the appropriate application.

The Edit Build Status page appears.
Select an alternate build status and click Apply Changes.

Tip:

Note that if you select Build Application Only during deployment, the only way to change this setting is change it on the Build Status page in Oracle Application Express Administration Services.

Viewing the Parsing Schemas Report

Instance administrators can use the Parsing Schemas report to identify the current parsing schema of each application in any workspace. This attribute identifies the Oracle database schema whose privileges are used when Application Express dynamically executes the application's SQL and PL/SQL code at runtime.

To view the Parsing Schemas report:

Log in to Oracle Application Express Administration Services. See "Logging In To Oracle Application Express Administration Services".
Click Manage Workspaces.
Under Manage Applications, click Parsing Schemas.

The Parsing Schemas page appears.

This page displays as an interactive report. To customize the report, use the Search bar at the top of the page. See "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.