OLAP Status

Is OLAP installed?

SELECT value

FROM v$option

WHERE parameter = 'OLAP';

Is OLAP used?

SELECT name "FEATURE", first_usage_date "FROM", last_usage_date "TO"

FROM DBA_FEATURE_USAGE_STATISTICS

WHERE name LIKE '%OLAP%';

OLAP component versions and status

column comp_id format a10

column comp_name format a30

column version format a15

column status format a10

SELECT comp_id, comp_name, version, status

FROM dba_registry

WHERE comp_name LIKE '%OLAP%';

OLAP Deinstall

Follow these steps to completely remove OLAP from the database.

It is possible that OLAP is shown as not installed, with no feature usage, and with a status of "OPTION OFF", but has a lot of related invalid objects. In this case you should also follow the steps below to remove all trace of OLAP from the database. In this scenario the relink step can be skipped.

Testing on a clone is recommended before attempting this procedure on a production database server.

Remove Analytic Workspaces

Check which workspaces exist...

column owner format a10

column aw_name format a20

SELECT owner, aw_name

FROM dba_aws;

For each row (except SYS.EXPRESS, which should be ignored), use the command below to delete each Workspace...

set serveroutput on

call dbms_aw.execute('AW DELETE &owner.&awname');

Skip this step if you receive the following errors:

Deinstall OLAP

sqlplus / as sysdba

spool /tmp/remove_olap.log

@?/olap/admin/catnoamd.sql

@?/olap/admin/olapidrp.plb

@?/olap/admin/catnoxoq.sql

@?/olap/admin/catnoaps.sql

@?/olap/admin/cwm2drop.sql

@?/rdbms/admin/utlrp.sql

spool off

Drop PUBLIC synonyms for OLAPSYS tables

spool /tmp/olapdrop.sql

select 'drop public synonym '||synonym_name||';'

from dba_synonyms

where owner='PUBLIC' and table_owner='OLAPSYS';

spool off

@/tmp/olapdrop.sql

Drop Remaining Objects

Many of these objects may not exist (ORA-01432 errors can be ignored)

Packages

DROP PACKAGE GENINTERRUPTABLEINTERFACE;

DROP PACKAGE DBMS_XSOQ_ODBO;

DROP PACKAGE BODY CWM2_OLAP_INSTALLER ;

DROP PACKAGE BODY SERVERINTERFACE;

DROP PACKAGE BODY METADATAPROVIDERINTERFACE;

DROP PACKAGE BODY DEFINITIONMANAGERINTERFACE;

DROP PACKAGE BODY DATAPROVIDERINTERFACE;

DROP PACKAGE BODY CONNECTIONINTERFACE;

DROP PACKAGE BODY DATABASEINTERFACE;

DROP PACKAGE BODY INTERRUPTABLEINTERFACE;

DROP PACKAGE BODY CURSORMANAGERINTERFACE;

Procedures

DROP PROCEDURE XOQ_VALIDATE ;

Functions

DROP FUNCTION INTERACTIONEXECUTE ;

DROP FUNCTION OLAPIBOOTSTRAP;

DROP FUNCTION OLAPIHANDSHAKE;

DROP FUNCTION OLAPI_MDX_ROWSET_TABLE;

Views

DROP VIEW SYS.OLAP_OLEDB_REG_ATTRS_PVT ;

DROP VIEW SYS.OLAP_OLEDB_CARDINALITY_PVT;

DROP VIEW SYS.OLAP_OLEDB_CUBEDIMS_PVT;

DROP VIEW SYS.OLAP_OLEDB_CUBES;

DROP VIEW SYS.OLAP_OLEDB_CUSTPROPS_PVT;

DROP VIEW SYS.OLAP_OLEDB_DFLT_MBR_PVT;

DROP VIEW SYS.OLAP_OLEDB_DIMENSIONS;

DROP VIEW SYS.OLAP_OLEDB_HIERARCHIES;

DROP VIEW SYS.OLAP_OLEDB_LEVELS;

DROP VIEW SYS.OLAP_OLEDB_LEVEL_TYPE_PVT;

DROP VIEW SYS.OLAP_OLEDB_LISTDIMS_PVT;

DROP VIEW SYS.OLAP_OLEDB_MEASDIMS_PVT;

DROP VIEW SYS.OLAP_OLEDB_MEASURES;

DROP VIEW SYS.OLAP_OLEDB_PROPERTIES;

DROP VIEW SYS.OLAP_OLEDB_STDHIERS_PVT;

DROP VIEW SYS.OLAP_OLEDB_STDLEVELS_PVT;

Types

DROP TYPE GENCONNECTIONPARAMETERINFOSEQU;

DROP TYPE GENCONNECTIONPARAMETERINFOSTRU;

DROP TYPE GENCONNECTIONSTRUCT;

DROP TYPE GENCONSTANTLISTDEFINITIONSTRUC;

DROP TYPE GENCONSTANTLISTVALUESUNION;

DROP TYPE GENCURSORBLOCK2SEQUENCE;

DROP TYPE GENCURSORBLOCK2STRUCT;

DROP TYPE GENCURSORBLOCKSEQUENCE;

DROP TYPE GENCURSORBLOCKSTRUCT;

DROP TYPE GENCURSORMANAGERSEQUENCE;

DROP TYPE GENCURSORVALUES2UNION;

DROP TYPE GENCURSORVALUESUNION;

DROP TYPE GENDATA2SEQUENCEUNION;

DROP TYPE GENDATABLOCK2SEQUENCE;

DROP TYPE GENDATABLOCK2STRUCT;

DROP TYPE GENDATABLOCKSEQUENCE;

DROP TYPE GENDATABLOCKSTRUCT;

DROP TYPE GENDATASEQUENCEUNION;

DROP TYPE GENDEFINITIONPOST92SEQUENCE;

DROP TYPE GENDEFINITIONPOST92UNION;

DROP TYPE GENDEFINITIONSEQUENCE;

DROP TYPE GENDEFINITIONUNION;

DROP TYPE GENEXPRESSEXCEPTION;

DROP TYPE GENINVALIDINDEXSPECIFICATIONEX;

DROP TYPE GENINVALIDMETADATAEXCEPTION;

DROP TYPE GENMETADATAPROPERTYBAGSEQUENCE;

DROP TYPE GENMETADATAPROPERTYBAGUNION;

DROP TYPE GENMETADATAPROPERTYSEQUENCE;

DROP TYPE GENMETADATAPROPERTYSTRUCT;

DROP TYPE GENMETADATAPROPERTYVALUESUNION;

DROP TYPE GENPARENTSTARTENDBLOCK2SEQUENC;

DROP TYPE GENPARENTSTARTENDBLOCK2UNION;

DROP TYPE GENPARENTSTARTENDBLOCKSEQUENCE;

DROP TYPE GENPARENTSTARTENDBLOCKUNION;

DROP TYPE GENREMOTEAPIVERSIONMISMATCHEXC;

DROP TYPE GENREMOTEAUTHENTICATIONEXCEPTI;

DROP TYPE GENREMOTEOBJECTCLOSEDEXCEPTION;

DROP TYPE GENREMOTESPECIFICATIONUPDATENE;

DROP TYPE GENREMOTETASKINTERRUPTEDEXCEPT;

DROP TYPE GENUNMATCHEDINPUTSEXCEPTION;

DROP TYPE OLAPI_MDX_ROWSET_IMPL_T;

Public Synonyms

DROP PUBLIC SYNONYM DBMS_XSOQ_ODBO;

DROP PUBLIC SYNONYM OLAPIBOOTSTRAP;

DROP PUBLIC SYNONYM OLAPIHANDSHAKE;

DROP PUBLIC SYNONYM OLAPI_MDX_ROWSET_IMPL_T;

DROP PUBLIC SYNONYM OLAPI_MDX_ROWSET_TABLE;

DROP PUBLIC SYNONYM OLAP_OLEDB_CUBES;

DROP PUBLIC SYNONYM OLAP_OLEDB_DIMENSIONS;

DROP PUBLIC SYNONYM OLAP_OLEDB_HIERARCHIES;

DROP PUBLIC SYNONYM OLAP_OLEDB_LEVELS;

DROP PUBLIC SYNONYM OLAP_OLEDB_MEASDIMS_PVT;

DROP PUBLIC SYNONYM OLAP_OLEDB_MEASURES;

DROP PUBLIC SYNONYM OLAP_OLEDB_PROPERTIES;

DROP PUBLIC SYNONYM MRV_OLAP2_HIER_CUSTOM_SORT ;

DROP PUBLIC SYNONYM MRV_OLAP_CWM1_AGGOP ;

DROP PUBLIC SYNONYM MRV_OLAP_CWM1_AGGORD ;

DROP PUBLIC SYNONYM MRV_OLAP2_AGGREGATION_USES ;

DROP PUBLIC SYNONYM MRV_OLAP2_ENTITY_PARAMETERS ;

DROP PUBLIC SYNONYM MRV_OLAP2_ENTITY_EXT_PARMS ;

DROP PUBLIC SYNONYM MRV_OLAP2_AWS ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_MAP_DIM_USE ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_PHYS_OBJ ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_PHYS_OBJ_PROP ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_MAP_MEAS_USE ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_MAP_ATTR_USE ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_DIMENSIONS ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_ATTRIBUTES ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_CUBES ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_CUBE_DIM_USES ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_DIM_LEVELS ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_CUBE_MEASURES ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_CUBE_AGG_SPECS ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_CUBE_AGG_MEAS ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_CUBE_AGG_LVL ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_CUBE_AGG_OP ;

DROP PUBLIC SYNONYM MRV_OLAP2_AW_DIM_HIER_LVL_ORD ;

DROP PUBLIC SYNONYM CWM2_OLAP_MR_SECURITY_INIT ;

DROP PUBLIC SYNONYM CWM2_OLAP_MR_SESSION_POP ;

DROP PUBLIC SYNONYM CWM2_OLAP_MR_CHECK_PRIVS ;

DROP PUBLIC SYNONYM CWM2_OLAP_METADATA_REFRESH ;

DROP PUBLIC SYNONYM OLAP_SYS_AW_ACCESS_CUBE_VIEW ;

DROP PUBLIC SYNONYM OLAP_SYS_AW_ACCESS_DIM_VIEW ;

DROP PUBLIC SYNONYM OLAP_SYS_AW_ENABLE_ACCESS_VIEW ;

DROP PUBLIC SYNONYM CWM2_OLAP_OLAPAPI_ENABLE ;

DROP PUBLIC SYNONYM CWM2_OLAP_AW_AWUTIL ;

DROP PUBLIC SYNONYM DBMS_AWM ;

DROP PUBLIC SYNONYM ALL_OLAP2_AWVIEWS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AWVIEWCOLS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AGGREGATION_USES ;

DROP PUBLIC SYNONYM DBA_OLAP2_AWVIEWS ;

DROP PUBLIC SYNONYM DBA_OLAP2_AWVIEWCOLS ;

DROP PUBLIC SYNONYM DBA_OLAP2_AGGREGATION_USES ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_DIMENSIONS ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_DIM_FILTERS ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_CUBE_FILTERS ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_CUBE_MEASURES ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_DIM_PARMS ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_CUBE_DIMS ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_CUBES ;

DROP PUBLIC SYNONYM ALL_AW_LOAD_CUBE_PARMS ;

DROP PUBLIC SYNONYM ALL_OLAP2_MV_CUBE_AGG_MEASURES ;

DROP PUBLIC SYNONYM ALL_OLAP2_MV_CUBE_AGG_LEVELS ;

DROP PUBLIC SYNONYM ALL_LOAD_CUBE_SEGWIDTH ;

DROP PUBLIC SYNONYM ALL_AW_CUBE_AGG_PLANS ;

DROP PUBLIC SYNONYM ALL_AW_CUBE_AGG_MEASURES ;

DROP PUBLIC SYNONYM ALL_AW_CUBE_AGG_LEVELS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AWS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CATALOGS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CATALOG_MEASURES ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_PHYS_OBJ ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_PHYS_OBJ_PROP ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_DIMENSIONS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_ATTRIBUTES ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CUBES ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CUBE_DIM_USES ;

DROP PUBLIC SYNONYM ALL_AW_DIM_ENABLED_VIEWS ;

DROP PUBLIC SYNONYM ALL_AW_CUBE_ENABLED_VIEWS ;

DROP PUBLIC SYNONYM ALL_AW_CUBE_ENABLED_HIERCOMBO ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_DIM_LEVELS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_DIM_HIER_LVL_ORD ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CUBE_MEASURES ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CUBE_AGG_SPECS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CUBE_AGG_MEAS ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CUBE_AGG_LVL ;

DROP PUBLIC SYNONYM ALL_OLAP2_AW_CUBE_AGG_OP ;

DROP PUBLIC SYNONYM MRV_OLAP1_POP_DIMENSIONS ;

DROP PUBLIC SYNONYM MRV_OLAP1_POP_CUBES ;

DROP PUBLIC SYNONYM MRV_OLAP2_POP_DIMENSIONS ;

DROP PUBLIC SYNONYM MRV_OLAP2_POP_CUBES ;

DROP PUBLIC SYNONYM MRV_OLAP2_AWVIEWS ;

DROP PUBLIC SYNONYM MRV_OLAP2_AWVIEWCOLS ;

DROP PUBLIC SYNONYM MRV_OLAP2_CATALOGS ;

DROP PUBLIC SYNONYM MRV_OLAP2_CATALOG_ENTITY_USES ;

DROP PUBLIC SYNONYM MRV_OLAP2_LISTDIMS_CC ;

DROP PUBLIC SYNONYM MRV_OLAP2_LISTDIMS ;

DROP PUBLIC SYNONYM MRV_OLAP2_DIM_HIERS ;

DROP PUBLIC SYNONYM MRV_OLAP2_HIERDIMS_CC ;

DROP PUBLIC SYNONYM MRV_OLAP2_HIERDIMS ;

DROP PUBLIC SYNONYM MRV_OLAP2_DIM_HIER_LEVEL_USES ;

DROP PUBLIC SYNONYM MRV_OLAP2_JOIN_KEY_COL_USES ;

DROP PUBLIC SYNONYM MRV_OLAP2_DIM_ATTRIBUTES ;

DROP PUBLIC SYNONYM MRV_OLAP2_ENTITY_DESC_USES ;

DROP PUBLIC SYNONYM MRV_OLAP2_DESCRIPTORS ;

DROP PUBLIC SYNONYM MRV_OLAP2_DIM_LEVEL_ATTR_MAPS ;

DROP PUBLIC SYNONYM MRV_OLAP2_CUBE_MEASURES ;

DROP PUBLIC SYNONYM MRV_OLAP2_FACTTBLKEYMAPS ;

DROP PUBLIC SYNONYM MRV_OLAP1_FACTTBLKEYMAPS ;

DROP PUBLIC SYNONYM MRV_OLAP2_FACTTBLFCTMAPS ;

DROP PUBLIC SYNONYM MRV_OLAP1_FACTTBLFCTMAPS ;

DROP PUBLIC SYNONYM MRV_OLAP2_HIERDIM_KEYCOL_MAP ;

DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERINFOSEQU;

DROP PUBLIC SYNONYM GENCONNECTIONPARAMETERINFOSTRU;

DROP PUBLIC SYNONYM GENCONNECTIONSTRUCT;

DROP PUBLIC SYNONYM GENCONSTANTLISTDEFINITIONSTRUC;

DROP PUBLIC SYNONYM GENCONSTANTLISTVALUESUNION;

DROP PUBLIC SYNONYM GENCURSORBLOCK2SEQUENCE;

DROP PUBLIC SYNONYM GENCURSORBLOCK2STRUCT;

DROP PUBLIC SYNONYM GENCURSORBLOCKSEQUENCE;

DROP PUBLIC SYNONYM GENCURSORBLOCKSTRUCT;

DROP PUBLIC SYNONYM GENCURSORMANAGERSEQUENCE;

DROP PUBLIC SYNONYM GENCURSORVALUES2UNION;

DROP PUBLIC SYNONYM GENCURSORVALUESUNION;

DROP PUBLIC SYNONYM GENDATA2SEQUENCEUNION;

DROP PUBLIC SYNONYM GENDATABLOCK2SEQUENCE;

DROP PUBLIC SYNONYM GENDATABLOCK2STRUCT;

DROP PUBLIC SYNONYM GENDATABLOCKSEQUENCE;

DROP PUBLIC SYNONYM GENDATABLOCKSTRUCT;

DROP PUBLIC SYNONYM GENDATASEQUENCEUNION;

DROP PUBLIC SYNONYM GENDEFINITIONPOST92SEQUENCE;

DROP PUBLIC SYNONYM GENDEFINITIONPOST92UNION;

DROP PUBLIC SYNONYM GENDEFINITIONSEQUENCE;

DROP PUBLIC SYNONYM GENDEFINITIONUNION;

DROP PUBLIC SYNONYM GENEXPRESSEXCEPTION;

DROP PUBLIC SYNONYM GENINTERRUPTABLEINTERFACE;

DROP PUBLIC SYNONYM GENINVALIDINDEXSPECIFICATIONEX;

DROP PUBLIC SYNONYM GENINVALIDMETADATAEXCEPTION;

DROP PUBLIC SYNONYM GENMETADATAPROPERTYBAGSEQUENCE;

DROP PUBLIC SYNONYM GENMETADATAPROPERTYBAGUNION;

DROP PUBLIC SYNONYM GENMETADATAPROPERTYSEQUENCE;

DROP PUBLIC SYNONYM GENMETADATAPROPERTYSTRUCT;

DROP PUBLIC SYNONYM GENMETADATAPROPERTYVALUESUNION;

DROP PUBLIC SYNONYM GENPARENTSTARTENDBLOCK2SEQUENC;

DROP PUBLIC SYNONYM GENPARENTSTARTENDBLOCK2UNION;

DROP PUBLIC SYNONYM GENPARENTSTARTENDBLOCKSEQUENCE;

DROP PUBLIC SYNONYM GENPARENTSTARTENDBLOCKUNION;

DROP PUBLIC SYNONYM GENREMOTEAPIVERSIONMISMATCHEXC;

DROP PUBLIC SYNONYM GENREMOTEAUTHENTICATIONEXCEPTI;

DROP PUBLIC SYNONYM GENREMOTEOBJECTCLOSEDEXCEPTION;

DROP PUBLIC SYNONYM GENREMOTESPECIFICATIONUPDATENE;

DROP PUBLIC SYNONYM GENREMOTETASKINTERRUPTEDEXCEPT;

DROP PUBLIC SYNONYM GENUNMATCHEDINPUTSEXCEPTION;

Modify the EXPDP datapump handler table

Modify the EXPDP datapump handler table to remove handlers used to export OLAP data:

select * from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';

delete from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';

commit;

Relink Oracle without the OLAP option

Shutdown the database instance and any listeners running from this ORACLE_HOME.

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk olap_off

make -f ins_rdbms.mk ioracle

Restart the instance and listener.

Bibliography