Managing Analytic Workspaces

To interact with Oracle OLAP, you must attach an analytic workspace to your session. From within SQL*Plus, you can use the following command to attach a workspace with read-only access.

SQL>execute dbms_aw.aw_attach ('awname');

Each analytic workspace is associated with a list of analytic workspaces. The read-only workspace EXPRESS.AW, which contains the OLAP engine code, is always attached last in the list. When you create a new workspace, it is attached first in the list by default.

You can reposition a workspace within the list by using keywords such as FIRST and LAST. For example, the following commands show how to move a workspace called GLOBAL.TEST2 from the second position to the first position on the list.

SQL>execute dbms_aw.execute ('aw list');

     TEST1 R/O UNCHANGED GLOBAL.TEST1 
     TEST2 R/O UNCHANGED GLOBAL.TEST2 
     EXPRESS R/O UNCHANGED SYS.EXPRESS 

SQL>execute dbms_aw.aw_attach ('test2', false, false, 'first');
SQL>execute dbms_aw.execute ('aw list');

     TEST2 R/O UNCHANGED GLOBAL.TEST2 
     TEST1 R/O UNCHANGED GLOBAL.TEST1 
     EXPRESS R/O UNCHANGED SYS.EXPRESS

From within SQL*Plus, you can rename workspaces and make copies of workspaces. If you have a workspace attached with read/write access, you can update the workspace and save your changes in the permanent database table where the workspace is stored. You must do a SQL COMMIT to save the workspace changes within the database.

The following commands make a copy of the objects and data in workspace test2 in a new workspace called test3, update test3, and commit the changes to the database.

SQL>execute dbms_aw.aw_copy('test2', 'test3');
SQL>execute dbms_aw.aw_update('test3');
SQL>commit;

Converting an Analytic Workspace to Oracle 10g Storage Format

Analytic workspaces are stored in tables within the Database. The storage format for Oracle 10g analytic workspaces is different from the storage format used in Oracle9i. Analytic workspace storage format is described in the Oracle OLAP Application Developer's Guide.

When you upgrade an Oracle9i database to Oracle 10g, the upgraded database is automatically in Oracle9i compatibility mode, and the analytic workspaces are still in 9i storage format. If you want to use new Oracle 10g OLAP features, such as dynamic enablement and multi-writer, you must use DBMS_AW.CONVERT to convert these workspaces to the new storage format.

Procedure: Convert an Analytic Workspace from 9i to10g Storage Format

To convert an Oracle9i compatible analytic workspace to Oracle 10g storage format, follow these steps:

Change the compatibility mode of the database to 10.0.0 or higher.
Log into the database with the identity of the analytic workspace.
In Oracle Database 10g SQL*Plus, use the following procedure to convert the workspace to the new storage format.
- Rename the analytic workspace to a name like aw_temp.
```
SQL>execute dbms_aw.aw_rename ('my_aw', 'aw_temp');
```
- Convert the workspace to 10g storage format in a workspace with the original name.
```
SQL>execute dbms_aw.convert ('aw_temp', 'my_aw');
```
  Note that standard form analytic workspaces typically include the workspace name in fully-qualified logical object names. For this reason, the upgraded workspace must have the same name as the original Oracle9i workspace.
Because you changed the Database compatibility mode to Oracle Database 10g, any new workspaces that you create are in the new storage format.

Procedure: Import a workspace from a 9i Database into a 10g Database

If you install Oracle Database 10g separately from your old Oracle9i Database installation, you must export the Oracle9i workspaces and import them into Oracle Database 10g. The export and import processes automatically convert the workspaces to the new storage format. Therefore you do not need to use DBMS_AW.CONVERT in this case.

Use the following procedure to export an Oracle9i analytic workspace and import it in an Oracle 10g database.

In Oracle Database 9i SQL*Plus, export the analytic workspace to the directory identified by the SCRIPTS directory object.

SQL>execute dbms_aw.execute ('aw attach ''awname''');
SQL>execute dbms_aw.execute ('allstat');
SQL>execute dbms_aw.execute ('cda scripts');
SQL>execute dbms_aw.execute ('export all to eif file ''filename''');

In Oracle 10g SQL*Plus, create a new workspace with the same name and schema, and import the EIF file from the SCRIPTS directory.

SQL>execute dbms_aw.execute ('aw create awname');
SQL>execute dbms_aw.execute ('cda scripts');
SQL>execute dbms_aw.execute ('import all from eif file ''filename''');
SQL>execute dbms_aw.execute ('update');

You can also use Oracle export and import utilities to move the entire schema, including the analytic workspaces to an Oracle 10g database. See Oracle Database Utilities and Oracle Database Upgrade Guide.