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

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