Please note that I'm by no means an OLAP expert; if you have your own observations or experiences to share, including corrections to any errors I might have made, please leave a comment. The last thing I want to do is spread misinformation! And, as always, remember: test systems exist for a reason, and instructions from Oracle Support should trump anything you read in this blog entry

"No objects to export" error when exporting AWs

The export process is explained thoroughly in Note 352306.1. You may encounter the following error, however, when attempting to export an empty workspace:

BEGIN dbms_aw.execute('export all to eif file ''EXPORT_DIR/AWTEST.eif'''); END;
*
ERROR at line 1:
ORA-33390: There are no objects to export.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at line 1

An export file will not be created, since there's no data in the workspace. Nonetheless, you will still need to recreate the AW in the 64-bit database, which leads us to the next section...

Before deleting AWs

In addition to gathering the OLAP workspace's name, schema, and tablespace, make sure that you make a note of how the AW is partitioned. This will allow you to more faithfully reconstruct the AW in the 64-bit database. Again, the basics can be found in Note 352306.1, except for a discussion of workspace partitioning. According to the documentation for DBMS_AW.AW_CREATE, by default, analytic workspaces are created with 8 partitions. Querying dba_segments seemed to tell a different story:

This initially confused me, until I found that the table created for the default workspace is actually comprised of two partitions, each comprised of 8 subpartitions. Apparently, "partition" means different things to different people:

So, before you delete the AWs in the 32-bit database, be sure to consult the data dictionary. In most cases, you'll probably see segment count of 16 in dba_segments (implying a default partitioning scheme). But there are exceptions...

There's always one goofball

One of those exceptions came when my query to get a count of AW segments returned a 1. Naturally, I was expecting an even number, so this came as a surprise. At first, I thought this might be a special case when specifying partnum=>1 when creating the workspace:

Please recall: Note 352306.1 recommends using the Analytic Workspace Manager (AWM) tool to recreate the AWs in the 64-bit database. If you want to create the AWs manually, I suggest engaging with Oracle Support to get their approval. The preceding examples are provided only for illustration of what's going on when the AW is created.

Importing AWs

I don't really have much to add here, other than:

If you had any empty AWs in the 32-bit system, you won't have anything to import for those workspaces, though hopefully you recreated them in the 64-bit system

Isn't this process the sort of thing that cries out to be scripted? Would I really resort to such cheap, obvious devices to foreshadow my next post? I would indeed!