Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas (Doc ID 565773.1) To BottomTo Bottom

Note that all the actions performed during the following sequence of commands do not affect components other than the OLAP option. When you choose to re-install OLAP, all the objects will be re-created and properly activated.

1. Run the removal scripts as outlined in Note 739032.1 How To Find Out If OLAP is Being Used And How To Remove OLAP
   
   
   -> conn / as sysdba
   SQL> @?/olap/admin/catnoamd.sql
   SQL> @?/olap/admin/olapidrp.plb
   SQL> @?/olap/admin/catnoaps.sql
   SQL> @?/olap/admin/catnoxoq.sql

Note that the steps above will drop the OLAPSYS schema.

Since it only contains objects related to the OLAP option, this objects are no longer required.
However, as a result objects defined in other schemas (specifically SYS and PUBLIC) may become invalid if they refer to definitions in the now missing OLAPSYS schema.

The following steps remove such objects.

2. Run the utlrp.sql script to determine invalid objects.

The following list shows the invalid objects to expect in Oracle 10g and 11g.
Note that these objects are all part of PUBLIC or SYS.
Objects in other schemas are not part of this cleanup operation.

SYS CWM2_OLAP_INSTALLER SYS INTERACTIONEXECUTE SYS OLAP_OLEDB_REG_ATTRS_PVT SYS XOQ_VALIDATE PUBLIC ALL_AW_CUBE_AGG_LEVELS PUBLIC ALL_AW_CUBE_AGG_MEASURES PUBLIC ALL_AW_CUBE_AGG_PLANS PUBLIC ALL_AW_CUBE_ENABLED_HIERCOMBO PUBLIC ALL_AW_CUBE_ENABLED_VIEWS PUBLIC ALL_AW_DIM_ENABLED_VIEWS PUBLIC ALL_AW_LOAD_CUBES PUBLIC ALL_AW_LOAD_CUBE_DIMS PUBLIC ALL_AW_LOAD_CUBE_FILTERS PUBLIC ALL_AW_LOAD_CUBE_MEASURES PUBLIC ALL_AW_LOAD_CUBE_PARMS PUBLIC ALL_AW_LOAD_DIMENSIONS PUBLIC ALL_AW_LOAD_DIM_FILTERS PUBLIC ALL_AW_LOAD_DIM_PARMS PUBLIC ALL_LOAD_CUBE_SEGWIDTH PUBLIC ALL_OLAP2_AGGREGATION_USES PUBLIC ALL_OLAP2_AWS PUBLIC ALL_OLAP2_AWVIEWCOLS PUBLIC ALL_OLAP2_AWVIEWS PUBLIC ALL_OLAP2_AW_ATTRIBUTES PUBLIC ALL_OLAP2_AW_CATALOGS PUBLIC ALL_OLAP2_AW_CATALOG_MEASURES PUBLIC ALL_OLAP2_AW_CUBES PUBLIC ALL_OLAP2_AW_CUBE_AGG_LVL PUBLIC ALL_OLAP2_AW_CUBE_AGG_MEAS PUBLIC ALL_OLAP2_AW_CUBE_AGG_OP PUBLIC ALL_OLAP2_AW_CUBE_AGG_SPECS PUBLIC ALL_OLAP2_AW_CUBE_DIM_USES PUBLIC ALL_OLAP2_AW_CUBE_MEASURES PUBLIC ALL_OLAP2_AW_DIMENSIONS PUBLIC ALL_OLAP2_AW_DIM_HIER_LVL_ORD PUBLIC ALL_OLAP2_AW_DIM_LEVELS PUBLIC ALL_OLAP2_AW_PHYS_OBJ PUBLIC ALL_OLAP2_AW_PHYS_OBJ_PROP PUBLIC ALL_OLAP2_MV_CUBE_AGG_LEVELS PUBLIC ALL_OLAP2_MV_CUBE_AGG_MEASURES PUBLIC CWM2_OLAP_AW_AWUTIL PUBLIC CWM2_OLAP_METADATA_REFRESH PUBLIC CWM2_OLAP_MR_CHECK_PRIVS PUBLIC CWM2_OLAP_MR_SECURITY_INIT PUBLIC CWM2_OLAP_MR_SESSION_POP PUBLIC CWM2_OLAP_OLAPAPI_ENABLE PUBLIC DBA_OLAP2_AGGREGATION_USES PUBLIC DBA_OLAP2_AWVIEWCOLS PUBLIC DBA_OLAP2_AWVIEWS PUBLIC DBMS_AWM PUBLIC MRV_OLAP1_FACTTBLFCTMAPS PUBLIC OLAP_OLEDB_REG_ATTRS_PVT

PUBLIC MRV_OLAP1_FACTTBLKEYMAPS PUBLIC MRV_OLAP1_POP_CUBES PUBLIC MRV_OLAP1_POP_DIMENSIONS PUBLIC MRV_OLAP2_AGGREGATION_USES PUBLIC MRV_OLAP2_AWS PUBLIC MRV_OLAP2_AWVIEWCOLS PUBLIC MRV_OLAP2_AWVIEWS PUBLIC MRV_OLAP2_AW_ATTRIBUTES PUBLIC MRV_OLAP2_AW_CUBES PUBLIC MRV_OLAP2_AW_CUBE_AGG_LVL PUBLIC MRV_OLAP2_AW_CUBE_AGG_MEAS PUBLIC MRV_OLAP2_AW_CUBE_AGG_OP PUBLIC MRV_OLAP2_AW_CUBE_AGG_SPECS PUBLIC MRV_OLAP2_AW_CUBE_DIM_USES PUBLIC MRV_OLAP2_AW_CUBE_MEASURES PUBLIC MRV_OLAP2_AW_DIMENSIONS PUBLIC MRV_OLAP2_AW_DIM_HIER_LVL_ORD PUBLIC MRV_OLAP2_AW_DIM_LEVELS PUBLIC MRV_OLAP2_AW_MAP_ATTR_USE PUBLIC MRV_OLAP2_AW_MAP_DIM_USE PUBLIC MRV_OLAP2_AW_MAP_MEAS_USE PUBLIC MRV_OLAP2_AW_PHYS_OBJ PUBLIC MRV_OLAP2_AW_PHYS_OBJ_PROP PUBLIC MRV_OLAP2_CATALOGS PUBLIC MRV_OLAP2_CATALOG_ENTITY_USES PUBLIC MRV_OLAP2_CUBE_MEASURES PUBLIC MRV_OLAP2_DESCRIPTORS PUBLIC MRV_OLAP2_DIM_ATTRIBUTES PUBLIC MRV_OLAP2_DIM_HIERS PUBLIC MRV_OLAP2_DIM_HIER_LEVEL_USES PUBLIC MRV_OLAP2_DIM_LEVEL_ATTR_MAPS PUBLIC MRV_OLAP2_ENTITY_DESC_USES PUBLIC MRV_OLAP2_ENTITY_EXT_PARMS PUBLIC MRV_OLAP2_ENTITY_PARAMETERS PUBLIC MRV_OLAP2_FACTTBLFCTMAPS PUBLIC MRV_OLAP2_FACTTBLKEYMAPS PUBLIC MRV_OLAP2_HIERDIMS PUBLIC MRV_OLAP2_HIERDIMS_CC PUBLIC MRV_OLAP2_HIERDIM_KEYCOL_MAP PUBLIC MRV_OLAP2_HIER_CUSTOM_SORT PUBLIC MRV_OLAP2_JOIN_KEY_COL_USES PUBLIC MRV_OLAP2_LISTDIMS PUBLIC MRV_OLAP2_LISTDIMS_CC PUBLIC MRV_OLAP2_POP_CUBES PUBLIC MRV_OLAP2_POP_DIMENSIONS PUBLIC MRV_OLAP_CWM1_AGGOP PUBLIC MRV_OLAP_CWM1_AGGORD PUBLIC OLAP_SYS_AW_ACCESS_CUBE_VIEW PUBLIC OLAP_SYS_AW_ACCESS_DIM_VIEW PUBLIC OLAP_SYS_AW_ENABLE_ACCESS_VIEW PUBLIC DBMS_ODM PUBLIC OLAPDIMVIEW PUBLIC OLAPFACTVIEW

The following list shows additional invalid objects to expect in Oracle 11g.: 

OLAPIBOOTSTRAP OLAPIHANDSHAKE GENINTERRUPTABLEINTERFACE OLAPI_MDX_ROWSET_TABLE GENEXPRESSEXCEPTION PUBLIC GENREMOTEOBJECTCLOSEDEXCEPTION GENREMOTEOBJECTCLOSEDEXCEPTION	GENREMOTETASKINTERRUPTEDEXCEPTION GENINVALIDMETADATAEXCEPTION GENINTERRUPTABLEINTERFACE GENDATASEQUENCEUNION GENDATA2SEQUENCEUNION GENCONNECTIONPARAMETERINFOSTRU

 Thes following list of objects (owned by SYS) were also approved by Development for deletion (part of OLAP table function szpport):

OLAPIMPL_T OLAPRC_TABLE OLAP_ATTRIBUTE_VISIBILITY$ OLAP_AW_VIEWS$ OLAP_BOOL_SRF OLAP_CONDITION OLAP_CUBE_BUILD_PROCESSES$ OLAP_DATE_SRF OLAP_DIMENSIONALITY$ OLAP_EXPRESSION OLAP_EXPRESSION_DATE OLAP_HIERARCHIES$ OLAP_IMPL_OPTIONS$ OLAP_MEASURES$ OLAP_MEAS_FOLDER_CONTENTS$ OLAP_MODEL_ASSIGNMENTS$ OLAP_MULTI_OPTIONS$ OLAP_SRF_T OLAP_TAB$ OLAP_TAB_COL$ OLAP_TEXT_SRF	OLAPRANCURIMPL_T OLAP_ATTRIBUTES$ OLAP_AW_DEPLOYMENT_CONTROLS$ OLAP_AW_VIEW_COLUMNS$ OLAP_CALCULATED_MEMBERS$ OLAP_CUBES$ OLAP_CUBE_DIMENSIONS$ OLAP_DESCRIPTIONS$ OLAP_DIM_LEVELS$ OLAP_EXPRESSION_BOOL OLAP_EXPRESSION_TEXT OLAP_HIER_LEVELS$ OLAP_MAPPINGS$ OLAP_MEASURE_FOLDERS$ OLAP_MODELS$ OLAP_MODEL_PARENTS$ OLAP_NUMBER_SRF OLAP_SYNTAX$ OLAP_TABLE OLAP_TAB_HIER$

The following list includes the set of PUBLIC synonyms that are related to OLAP table functions and can be dropped as well:

PUBLIC OLAPRC_TABLE PUBLIC OLAP_CONDITION PUBLIC OLAP_EXPRESSION PUBLIC OLAP_EXPRESSION_DATE PUBLIC OLAP_NUMBER_SRF PUBLIC OLAP_TABLE	PUBLIC OLAP_BOOL_SRF PUBLIC OLAP_DATE_SRF PUBLIC OLAP_EXPRESSION_BOOL PUBLIC OLAP_EXPRESSION_TEXT PUBLIC OLAP_SRF_T PUBLIC OLAP_TEXT_SRF

3. All the objects listed in step 2 can be deleted, since they are used exclusively by the OLAP option.

To make this easier, use the following SQL script to determine all PUBLIC synonyms referring to objects in the OLAPSYS schema:
This will generate a script that drops the listed objects from the database.

SQL>spool olapdrop.sql
SQL> select 'drop public synonym ' || synonym_name || ';'
       from dba_synonyms
       where owner='PUBLIC' and table_owner='OLAPSYS';
SQL>spool off

4. Now drop any objects owned by PUBLIC or SYS that is still present from the list in step 2.

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

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

This step is required because the datapump export  (EXPDP) uses these handlers to copy objects from the database to external files (dump files). In order to properly perform this action for OLAP objects, a handler was registered at the time OLAP was installed or configured. Since the handler has now been removed, it also needs to be purged from the list of handlers. Not doing so would cause every datapump export to fail in the future.

6. For Oracle 11g, remove the Export Extended Info hander; more details can be found in Note 1353491.1: 

SQL>delete from exppkgact$ where package = 'DBMS_CUBE_EXP' and schema = 'SYS';
SQL>commit;