The status of the OLAP components in the dba_registry is LOADED OR UPGRADED


For 9i OLAP is still functional in UPGRADED or LOADED Status.

For 10G/11g, OLAP is only functional with a VALID status.


To check the status of the components, use the following Sql command:

SQL> select substr(comp_id,1,20)"COMP_ID",substr(comp_name,1,25)"Component
Name",substr(version,1,10) "version",status from dba_registry;
 

The results:

 

COMP_ID     Component                               Name version              STATUS

-------------------- ------------------------- ---------- ---------------------------------------------

APS            OLAP Analytic Workspace           10.2.0.3                     LOADED
XOQ           Oracle OLAP API                        10.2.0.3                      LOADED
AMD           OLAP Catalog                              10.2.0.3                      LOADED


Solution:


1) Check if there are any invalid objects in the OLAPSYS schema.


SQL> select owner, object_name, object_type, status from dba_objects where
status = 'INVALID' and OWNER = 'OLAPSYS' ;
 

b) If objects are invalid, recompile all individual objects logged in as OLAPSYS
.


SQL> alter package <package_name> compile; (package specification)
SQL> alter package <package_name> compile body; (package body)
SQL> alter view <view_name> compile; (view)
 

2) After validating all the OLAPSYS objects in the dba_registry, update the registry .

SQL> connect / as sysdba
SQL> execute dbms_registry.valid('AMD');

SQL> execute dbms_registry.valid('APS');

SQL> execute dbms_registry.valid('XOQ');
 
3. Confirm the status in dba_regisrty:

SQL> select substr(comp_id,1,20)"COMP_ID",substr(comp_name,1,25)"Component
Name",substr(version,1,10) "version",status from dba_registry;
 


Note: You should only do the above if all olap objects are valid. If not, to validate individual components in the dba_registry, reload the invalid component using the following scripts. These scripts are for 10G and may not be available for 9i.
Must log in as sysdba:

ORACLE_HOME/rdbms/admin/olappl.sql  --- This script will reload Olap Analytic workspace.
ORACLE_HOME/olap/admin/apsrelod.sql ---  This script validates Olap analytic workspace.
ORACLE_HOME/olap/admin/xoqrelod.sql --- Reloads Oracle OLAP API .
ORACLE_HOME/olap/admin/amdrelod.sql --- Reloads OLAP CATALOG .
ORACLE_HOME/rdbms/admin/utlrp.sql  ----  This script recompiles any invalid objects.



If you like please follow and comment