Analytic Workspace之导入导出

在Oracle OLAP中，Analytic workspace的概念有点类似与数据库中的schema，也是一组相关对象的集合。但是Analytic workspace会使用一个名为aw$name的 table来实际的存储analytic workspace中的对象(dimension,cube,level,attribute等)。有时候，我们可能需要将一个analytic workspace从一个系统迁移到另外一个系统，也可能需要将一个analytic workspace的内容迁移到另外一个analytic workspace中，利用Analytic Workspace Manager可以在图形界面下完成该任务，首先将一个aw导出为EIF文件，然后将EIF导入到另外一个aw即可。

除了awm，也可以通过dbms_aw包来实现同样的功能。

1.版本

GLOBAL @ ning > select * from v $ version ;
 
BANNER
-------------------------------------------------------------- --
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL / SQL Release 10.2.0.3.0 - Production
CORE     10.2.0.3.0       Production
TNS for 32 - bit Windows : Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

 

2.创建一个directory并授权

SYS @ ning > create directory dir_aw as ' d: /' ;
 
Directory created.
 
SYS@ning>grant read,write on directory dir_aw to global;
 
Grant succeeded.

3.将名为global的aw导出为EIF文件

SYS @ ning > conn global / global
Connected .
 
GLOBAL @ ning > set serveroutput on
 
--首先 attach 该 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' aw attach global ' ) ;
IMPORTANT : Analytic workspace GLOBAL is read - only . Therefore , you will not be
able to use the UPDATE command to save changes to it .
 
PL / SQL procedure successfully completed .
 
--将当前目录设置为 dir_aw
GLOBAL @ ning > exec dbms_aw . execute ( ' CDA DIR_AW ' ) ;
The current directory is DIR_AW .
 
PL / SQL procedure successfully completed .
 
--导出为 EIF 文件
GLOBAL @ ning > exec dbms_aw . execute ( ' EXPORT ALL TO EIF FILE '' GLOBAL.EIF '' NOTEMPDATA ' ) ;
 
PL / SQL procedure successfully completed .
 
-- Detach 该 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' AW DETACH global ' ) ;
 
PL / SQL procedure successfully completed .

4.导入EIF文件为已经存在的AW中

--首先以 RW 模式 attach 欲导入的 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' AW ATTACH global RW ' )
 
PL / SQL procedure successfully completed .
 
--将当前目录设置为 dir_aw
GLOBAL @ ning > exec dbms_aw . execute ( ' CDA DIR_AW ' ) ;
The current directory is DIR_AW .
 
PL / SQL procedure successfully completed .
 
--导入 EIF 文件到已经存在的 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' IMPORT ALL FROM EIF FILE '' globaL.eif '' UPDATE ' ) ;
 
PL / SQL procedure successfully completed .
 
-- Detach 该 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' AW DETACH GLOBAL ' ) ;
 
PL / SQL procedure successfully completed .
 
GLOBAL @ ning > commit ;
 
Commit complete .

5.导入EIF文件为新的aw

--创建新的 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' aw create global_new ' ) ;
 
PL / SQL procedure successfully completed .
 
--设置当前目录
GLOBAL @ ning > exec dbms_aw . execute ( ' CDA DIR_AW ' ) ;
The current directory is DIR_AW .
 
PL / SQL procedure successfully completed .
 
--导入 EIF 文件到已经存在的 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' IMPORT ALL FROM EIF FILE '' globaL.eif '' UPDATE ' ) ;
 
PL / SQL procedure successfully completed .
 
-- Detach 该 aw
GLOBAL @ ning > exec dbms_aw . execute ( ' AW DETACH GLOBAL ' ) ;
 
PL / SQL procedure successfully completed .
 
GLOBAL @ ning > commit ;
 
Commit complete .
 
--查看系统中所有的 aw
 
GLOBAL @ ning > exec dbms_aw . execute ( ' aw list ' ) ;
GLOBAL_NEW * R / W UNCHANGED GLOBAL . GLOBAL_NEW
GLOBAL     R / W UNCHANGED GLOBAL . GLOBAL
EXPRESS     R / O UNCHANGED SYS . EXPRESS

参考文章：Metalink Note:437595.1

作者：NinGoo

本文网址：http://www.ningoo.net/html/2007/export_and_import_analytic_workspace.html