ORA-06502 appears when an AWR report is generated

ORA-06502 appears when an AWR report is generated

The following error occurred recently when generating an AWR report for a system: ORA-06502: PL/SQL: numeric or value error: character string buffer too small, then the process of generating the AWR report is terminated. Check the generated AWR report. If the report is incomplete, the AWR report is completed in the Complete List of SQL Text section, finally, it ends with a very long SQL statement, and the SQL statement is not completely displayed. After checking the MOS, we found that this was caused by a Bug 7833620 (refer to the MOS document Doc ID 1303342.1). However, we were a bit tempted to generate an AWR report and apply patches, so I decided to find a way to bypass this BUG.

According to the above analysis, the cause of AWR report generation failure is that there is a particularly long SQL statement in the system. With this in mind, the next step is to make it easy. The SQL text that generates the AWR report is from WRH $ _ SQLTEXT. You only need to cut the SQL text in the base table, we should be able to generate the AWR report correctly. The WRH $ _ SQLTEXT base table stores the SQL text information during the snapshot period. Operations on this table will not affect the running of system SQL statements, however, operations on the base table are always very dangerous. We recommend that you do not directly operate on the generation system. You can import the produced AWR data to the test environment to generate an AWR report. For more information about AWR import/export, see.

The structure of the WRH $ _ SQLTEXT base table is as follows:

SQL> desc WRH $ _ SQLTEXT

• Name Null? Type
• -----------------------------------------------------------------------------
• SNAP_ID