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                      LOADED
XOQ           Oracle OLAP API                                    LOADED
AMD           OLAP Catalog                                          LOADED


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