Clarity Upgrade fails with ORA-01720 on CMN_INDX_MONITORING_V

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When running Clarity upgrade, an error is thrown on Oracle:

java.sql.BatchUpdateException: error occurred during batching: ORA-01720: grant option does not exist for 'SYS.USER_OBJECT_USAGE'
SQL Text:
CREATE OR REPLACE FORCE
VIEW CMN_INDX_MONITORING_V ( TABLE_NAME, INDEX_NAME, MONITORING, USED, START_MONITORING, END_MONITORING ) AS
SELECT TABLE_NAME,
               INDEX_NAME,
               MONITORING,
               USED,
               START_MONITORING,
               END_MONTIROING
                 FROM      USER_OBJECT_USAGE

Environment

Clarity 16.4.2 with Oracle 

Resolution

The workaround may require the view to be dropped as changing permissions whilst the object is already set may not provide them

Workaround:

1. Drop the existing view 

   DROP VIEW CMN_INDX_MONITORING_V;

2. Grant SELECT specifically, as SYS or SYSDBA -- replace Test with exact user name

   GRANT SELECT ON SYS.USER_OBJECT_USAGE TO <TEST>WITH GRANT OPTION;

    

3. Restart your Clarity Upgrade.
   
   Once the old view and its conflicting grants are gone the Clarity install will run its CREATE OR REPLACE VIEW statement. It should succeed and then automatically reapply the grants

4. After the upgrade succeeds, ensure the view CMN_INDX_MONITORING_V is present in the schema