Users unable to change dates in CMR reports - Addressing ORA-00600 error message generated when ETL job is ran

Version 1



    When trying to change the dates on a CMR report users are unable to choose recent dates.



    Identifying the Problem




    • Version: Jive Custom (On Premise)
    • Database: Oracle DB





    Users are unable to change the dates in CMR reports.


    You will also be able to find errors in the sbs.log file related to "ORA-00600", where ETL summary tasks are failing to run:

    14 Jan 2014 01:27:11,141 [http-] [52708:username:REGULAR] ERROR tasks.AnalyticsChartSummationETL - Opps an error occured while summarizing

    org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT d.begin_ts, q.user_id, q.container_id, q.container_type, COUNT(DISTINCT q.thread_id) * 1 FROM jivedw_question q, jivedw_day d, jivedw_user_type y, (SELECT m.thread_id AS tid, MIN(os.status_ts) AS ts FROM jivedw_message m, jivedw_question q, jivedw_object_status os WHERE m.parent_message_id <> 0 AND q.thread_id = m.thread_id AND m.message_id = os.object_id AND os.object_type =  2 AND os.status = 2 GROUP BY m.thread_id HAVING MIN(os.status_ts) >= ? AND MIN(os.status_ts) <= ? )t1 WHERE q.user_id = y.user_id AND (y.external = 0 OR y.external = 2) AND y.visible = 1 AND q.thread_id = tid AND q.thread_id NOT IN (SELECT DISTINCT object_id FROM jivedw_deleted_objects WHERE object_type IN ( 1, 27) AND deleted_ts < ? ) AND q.thread_id NOT IN (SELECT DISTINCT o.object_id FROM jivedw_moved_objects o, jivedw_message m WHERE o.object_id = m.thread_id AND m.parent_message_id <> 0 AND o.moved_ts > m.creation_ts AND o.object_type IN ( 1, 27) ) AND t1.ts >= d.begin_ts AND t1.ts <= d.end_ts GROUP BY d.begin_ts, q.user_id, q.container_id, q.container_type ]; SQL state [60000]; error code [600]; ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []

    ; nested exception is java.sql.SQLException: ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []


    It was found that the database tables referenced in the Jive application had not been analyzed in over a month. This was causing the PLAN to return many more rows than needed.





    Reanalyze all the tables in the database schema using ANALYZE command.


    If you continue to see ORA-00600 errors after re-analyzing the database then please file a support case with Jive to further investigate the issue.