Invalid OLAP Catalog And Missing CWM$ Tables

Error:

, cwm$classificationentry ce

ERROR at line 10:

ORA-00942: table or view does not exist

Reason:

This will occur if you upgrade the database when Olap Catalog is Invalid but could also occur under different circumstances. The cwm$ tables are created by onetabs.sql.

Here is the logic in olap.sql.

select count(*) into isthere from dba_users where username ='OLAPSYS';

if ( isthere > 0 ) then

:file_name := 'amdrelod.sql';

else

:file_name := 'catamd.sql';

end if;

The call to onetabs.sql is in catamd.sql. If the user OLAPSYS does not exist, then catamd.sql is executed. but if OLAPSYS user exists amdrelod.sql is executed.

Solution:

conn / as sysdba

drop user "OLAPSYS" CASCADE;

Then re-run the olap.sql commands:

SQL> spool add_olap.log

SQL> select name from v$database;

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

SQL>@?/rdbms/admin/utlrp.sql;

SQL> column object_name format a35

SQL> column object_type format a25

SQL> column owner format a15

SQL> column comp_name format a35

SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry;

SQL> SELECT owner, object_name, object_type from dba_objects where status <> 'VALID' order by owner, object_type;

SQL> spool off

SQL> quit

If you like please follow and comment

Invalid OLAP Catalog And Missing CWM$ Tables