Invalid OLAP Catalog And Missing CWM$ Tables


, cwm$classificationentry ce
ERROR at line 10:
ORA-00942: table or view does not exist


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';
: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.


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> 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