My Oracle Support Banner

How To Use Transportable Tablespaces To Copy OLAP Analytic Workspaces (Doc ID 438035.1)

Last updated on OCTOBER 29, 2019

Applies to:

Oracle OLAP - Version and later
Information in this document applies to any platform.


This document describes how to rapidly copy a tablespace holding an Analytic Workspace using the Data Pump expdp and impdp facility.

This is different from utilizing the OLAP component's facility of exporting an Analytic Workspace to a so-called EIF file and re-creating the Analytic Workspace by importing the EIF file.


Transportable Tablespace is the concept that permits copying tablespaces without having to write all the data contained in it to a dump file and import the data from there. Only control information (metadata) is written to the dump file and the data is copied using the datafiles.

Source instance
is the instance that holds the Analytic Workspace data that should be copied to another instance.

Target instance is the instance that should receive the Analytic Workspace from the source instance.


The source and target instances that are involved in this process are of identical platform. The Analytic Workspace internal structure will be copied verbatim, and no conversions will take place. This process will not work if different platforms (say, Windows 32 and Linux x86-64) are involved. If you are using different platforms, you need to use EIF file transfer instead.

The process described here handles entire tablespaces. Analytic Workspaces are stored in tables named AW$name, where name is the name of the Analytic Workspace.

At creation time, Analytic Workspaces can be forced to reside in a dedicated tablespace. This is the preferred scenario since the process described here moved entire tablespaces.

This note does not cover Automatic Storage Management (ASM). If one or both of the instances use ASM, an extra step needs to be performed to move the datafiles out of ASM and / or into ASM. This can slow the process down significantly.

Please note: If the tablespace has already been transported before, then the AW will not be directly useful.
There is an article 853335.1 that covers the additional steps required.

You can check this by checking the dba_tablespaces view:

If the PLUGGED_IN value is YES, then please follow the instructions in this Note.



Example useage

Load new data into an Analytic Workspace using a staging instance.
The data is loaded using the staging instance, the production system is instance for queries.
When all the data has been loaded and verified, the AW can be copied to the production instance (or made available through a shared disk storage device).

The process of activating the new version of the Analytic Workspace can be accomplished very quickly as the duration of the switchover depends on the number of objects, not their size.

Since the data is loaded on the staging system and queried on the production system at different times, the RDBMS does not have to maintain too many copies of older data during the data load. In contrast, loading data into an Analytic Workspace while at the same time querying it will require overhead to keep data in a consistent state.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.