Peculiar problem of Data Mining and OLAP status in dba_registry

As part of the 10g upgrade, we have the step of installing OLAP and Data Mining. For Data Mining following SQL is run:

@$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP;

For OLAP, the following SQL is run:

@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;

Whenever these two sqls were run one after the other, we would end of with OLAP and Data Mining invalids with the status of Data Mining showing up as INVALID and OLAP showing up as LOADING.

To investigate, we checked the contents of dminst.sql:

@@odmcrt.sql &&1 &&2

execute sys.dbms_registry.loading('ODM','Oracle Data Mining','validate_odm','DMS
YS',NULL,NULL);
==========================================
@@odmproc.sql

alter session set current_schema = "DMSYS";

@@catodm.sql

execute sys.dbms_registry.loaded('ODM');

execute sys.validate_odm;
===========================================

Notice the line in bold. It is setting the current schema to DMSYS. That means if you run olap.sql without disconnecting your session from sqlplus, the sql statements in olap.sql are going to run on DMSYS schema, thus invalidating DMSYS as well as OLAP. So the way out is to do this:

sqlplus /nolog
connect / as sysdba
@$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP;
exit

In the above line you may exit sqlplus or reconnect by giving command: connect / as sysdba thus disconnecting the existing session.

sqlplus /nolog
connect / as sysdba
@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;