java olap oracle_【案例】Oracle数据库升级OLAP组件异常 删除OLAP组件

天萃荷净

Oracle研究中心案例分析：在升级完一套Oracle数据库后发现OLAP组件出现异常，详细记录如何删除OLAP组件的过程。

本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: How to remove OLAP组件？

今天在升级一套10204 db时，发现OLAP组件有异常，由于该组件对该系统来讲无用，故可以remove，查询dba_registry信息如下：

SQL> Col comp_name FOR a35

SQL> Col STATUS FOR a15

SQL> SET LINES 150

SQL> SELECT comp_name,version,STATUS FROM dba_registry;

COMP_NAME                           VERSION          STATUS

----------------------------------- ---------------- ---------------

Oracle DATABASE Catalog Views       10.2.0.4.0       VALID

Oracle DATABASE Packages AND Types  10.2.0.4.0       VALID

Oracle Workspace Manager            10.2.0.4.3       VALID

JServer JAVA Virtual Machine        10.2.0.4.0       VALID

Oracle XDK                          10.2.0.4.0       VALID

Oracle DATABASE Java Packages       10.2.0.4.0       VALID

Oracle Expression FILTER            10.2.0.4.0       VALID

Oracle DATA Mining                  10.2.0.4.0       VALID

Oracle Text                         10.2.0.4.0       VALID

Oracle XML DATABASE                 10.2.0.4.0       VALID

Oracle Rules Manager                10.2.0.4.0       VALID

Oracle interMedia                   10.2.0.4.0       VALID

OLAP Analytic Workspace             10.2.0.4.0       INVALID

Oracle OLAP API                     10.2.0.4.0       INVALID

OLAP Catalog                        10.2.0.4.0       INVALID

Spatial                             10.2.0.4.0       VALID

Oracle Enterprise Manager           10.2.0.4.0       VALID

17 ROWS selected.

SQL> SELECT VALUE

2  FROM v$OPTION

3  WHERE PARAMETER = 'OLAP';

VALUE

-------------------------------------------

FALSE

SQL> SELECT owner, aw_name FROM dba_aws;

OWNER                AW_NAME

-------------------- ----------------------

SYS                  EXPRESS

SYS                  AWMD

SYS                  AWCREATE

SYS                  AWCREATE10G

SYS                  AWXML

SYS                  AWREPORT

6 ROWS selected.

metalink 上的一篇文档中提供了如何remove OLAP组件的方法，对于10g~11g版本，

执行如下几个sql scripts即可：

SQL> @ /olap/admin/catnoamd.SQL

SQL> @ /olap/admin/olapidrp.plb

SQL> @ /olap/admin/catnoaps.SQL

SQL> @ /olap/admin/catnoxoq.SQL

SQL> @ /olap/admin/cwm2drop.SQL

++++++ 执行完成以后，再次查询dba_registry，将会发现OLAP组件的状态为remove，如下：++++++

COMP_NAME                                VERSION           STATUS

---------------------------------------- ----------------- ----------------------

Oracle DATABASE Catalog Views            10.2.0.4.0        VALID

Oracle DATABASE Packages AND Types       10.2.0.4.0        VALID

Oracle Workspace Manager                 10.2.0.4.3        VALID

JServer JAVA Virtual Machine             10.2.0.4.0        VALID

Oracle XDK                               10.2.0.4.0        VALID

Oracle DATABASE Java Packages            10.2.0.4.0        VALID

Oracle Expression FILTER                 10.2.0.4.0        VALID

Oracle DATA Mining                       10.2.0.4.0        VALID

Oracle Text                              10.2.0.4.0        VALID

Oracle XML DATABASE                      10.2.0.4.0        VALID

Oracle Rules Manager                     10.2.0.4.0        VALID

Oracle interMedia                        10.2.0.4.0        VALID

OLAP Analytic Workspace                  10.2.0.4.0        REMOVED

Oracle OLAP API                          10.2.0.4.0        REMOVED

Spatial                                  10.2.0.4.0        VALID

Oracle Enterprise Manager                10.2.0.4.0        VALID

16 ROWS selected.

如果是10g以前版本，可以使用如下方法：

1) drop the olapsys schema;

2) remove the olap option from a Oracle9i Database:

% sqlplus / nolog

SQL> conn / as sysdba

SQL> @ /cwmlite/admin/onedrop.sql

SQL> @ /olap/admin/olapidrp.sql

3) create the database using the Database Creation Assistant (dbca)

and then remove the olap script from the list of scripts to be executed.

更为详细的信息，大家可以参考如下两篇文档：

How To Find Out If OLAP Is Being Used And How To Remove OLAP Oracleoracleplus.net[ID 739032.1]

How To Remove the Oracle OLAP API Objects From 9i and 11g Databases [ID 278111.1]

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle数据库升级OLAP组件异常 删除OLAP组件