Oracle 10g出现ORA-04030错误如何查询导致内存不足的SQL

云上老周

2026-10-17

ORA-04030高内存消耗SQL定位查询语句

ORA-04030为Oracle进程私有内存（PGA）分配不足触发的报错，可通过以下系统视图查询定位内存占用过高的SQL：

实时查询当前TOP10高PGA占用SQL

SELECT *
FROM (
    SELECT 
        s.sid,
        s.serial#,
        s.username,
        s.sql_id,
        sa.sql_text,
        ROUND(p.pga_alloc_mem/1024/1024,2) pga_alloc_mb,
        ROUND(p.pga_used_mem/1024/1024,2) pga_used_mb,
        ROUND(p.pga_max_mem/1024/1024,2) pga_max_mb
    FROM v$session s
    INNER JOIN v$sqlarea sa ON s.sql_id = sa.sql_id
    INNER JOIN v$process p ON s.paddr = p.addr
    ORDER BY p.pga_alloc_mem DESC
)
WHERE ROWNUM <= 10;

返回结果中pga_alloc_mb为进程当前分配的PGA总大小，数值越高对应SQL引发内存不足的概率越高。

查询历史时间段高PGA消耗SQL（需AWR功能正常开启）

SELECT *
FROM (
    SELECT 
        sql_id,
        sql_text,
        ROUND(MAX(pga_allocated)/1024/1024,2) max_pga_mb,
        TO_CHAR(begin_interval_time,'YYYY-MM-DD HH24:MI:SS') snap_start_time
    FROM dba_hist_sqlstat st
    INNER JOIN dba_hist_snapshot sn ON st.snap_id = sn.snap_id AND st.instance_number = sn.instance_number
    WHERE begin_interval_time BETWEEN TO_DATE('替换为报错开始时间','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('替换为报错结束时间','YYYY-MM-DD HH24:MI:SS')
    GROUP BY sql_id, sql_text, begin_interval_time
    ORDER BY max_pga_mb DESC
)
WHERE ROWNUM <= 10;

该语句适用于报错会话已断开、无法查询实时视图的场景，需替换语句中的时间范围为报错前后1小时内的时间。

直接查询已触发ORA-04030的SQL记录（需开启SQL监控功能）

SELECT 
    sql_id,
    sql_text,
    ROUND(pga_allocated/1024/1024,2) pga_used_mb,
    TO_CHAR(last_refresh_time,'YYYY-MM-DD HH24:MI:SS') error_time
FROM v$sql_monitor
WHERE error_code = 4030
ORDER BY last_refresh_time DESC;