Oracle plugin tablespace stats query returns ORA-40478: output value too large (maximum: 4000).

XMLWordPrintable

    • Type: Incident report
    • Resolution: Unresolved
    • Priority: Trivial
    • None
    • Affects Version/s: None
    • Component/s: None
    • None

      Steps to reproduce:

      1. Have EL8 linux agent 2 with oracle plugin ( for example version 7.0.19)

      2. Have a lot of tablespaces or files

      3. run the query for tablespace stats item

      The query will produce:

      ORA-40478: output value too large (maximum: 4000)

      for item with key oracle.ts.stats["\{$ORACLE.CONNSTRING}","\{$ORACLE.USER}","\{$ORACLE.PASSWORD}","\{$ORACLE.SERVICE}"]

      Expected result:

      stats for database

       

      Likely issue

      There has been a change to the query, adding more nested querys for converting the result to json, but not all tje json items are returned as clob, thus they are handled as varchar and hitting the character limit on bigger databases.

      Suggestion: handle all json objects as lobs, for example:

      SELECT JSON_ARRAYAGG(
               JSON_OBJECT(CON_NAME VALUE
                           JSON_ARRAYAGG(
                                   JSON_OBJECT(TABLESPACE_NAME VALUE
                                               JSON_OBJECT(
                                                       'contents' VALUE CONTENTS,
                                                       'file_bytes' VALUE FILE_BYTES,
                                                       'max_bytes' VALUE MAX_BYTES,
                                                       'free_bytes' VALUE FREE_BYTES,
                                                       'used_bytes' VALUE USED_BYTES,
                                                       'used_pct_max' VALUE USED_PCT_MAX,
                                                       'used_file_pct' VALUE USED_FILE_PCT,
                                                       'used_from_max_pct' VALUE USED_FROM_MAX_PCT,
                                                       'status' VALUE STATUS
                                                   ) RETURNING CLOB
                                       ) RETURNING CLOB
                               ) RETURNING CLOB
                   ) RETURNING CLOB
           ) 
FROM .......

            Assignee:
            Wojciech Rawicki
            Reporter:
            karel laid
            Votes:
            3 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: