Analytic Workspace之导入导出

在Oracle OLAP中，Analytic workspace的概念有点类似与数据库中的schema，也是一组相关对象的集合。但是Analytic workspace会使用一个名为aw$name的table来实际的存储analyticworkspace中的对象(dimension,cube,level,attribute等)。有时候，我们可能需要将一个analyticworkspace从一个系统迁移到另外一个系统，也可能需要将一个analytic workspace的内容迁移到另外一个analyticworkspace中，利用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