Oracle数据库Buffer Overflow问题求助（1000000字节限制）

问题描述 #

在Oracle数据库执行PL/SQL脚本时触发ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes错误，Stack Overflow上的常规缓冲区扩容方案均无效，现提供脚本代码、已尝试方案及完整报错信息，请求排查代码错误。

原PL/SQL代码 #

set serveroutput on;

DECLARE
    c_id    t_person.id%TYPE;
    c_value t_person_communication_method.value%TYPE;
    c_comm_id t_person_communication_method.id%TYPE;
    CURSOR cr IS
 SELECT
        fppc.id as id, fppc.value as value, fppc.person_id as person_id
    FROM
        (
            SELECT
                ppc.*,
                ROW_NUMBER()
                OVER(PARTITION BY ppc.value
                     ORDER BY
                         ppc.value
                ) rn
            FROM
                (
                    SELECT
                        cm.id,
                        cm.person_id,
                        p.first_name,
                        p.last_name,
                        p.business_flow_id,
                        cm.type,
                        cm.value,
                        cm.is_verified,
                        cm.created_date,
                        cm.created_channel
                    FROM
                             t_person p
                        JOIN t_person_communication_method cm ON p.id = cm.person_id
                    WHERE
                           cm.type = 'EMAIL'
                        AND cm.is_verified = 'Y'
                    ORDER BY
                        p.id ASC
                ) ppc
        ) fppc
    WHERE
        fppc.rn > 1;

BEGIN
    OPEN cr;
    LOOP
       update t_person_login set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person_login_history set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person set first_name ='anonymous',last_name ='anonymous' where id = c_id;
        update t_person_communication_method set value ='anonymous@gmail.com' where type = 'EMAIL' and person_id =c_id;
        update t_person_communication_method set value ='00000000000' where type = 'PHONE' and person_id =c_id and id=c_comm_id;
        update t_person_comm_method_history set value = 'anonymous@gmail.com' where type ='EMAIL' and person_id =c_id;
        update t_person_address set postcode ='anonymous',address_1 ='anonymous',city='anonymous',county ='anonymous' where person_id =c_id;        
        EXIT WHEN cr%notfound;
        dbms_output.put_line(c_id || ' ' || c_value || '' || c_comm_id);
    END LOOP;

    CLOSE cr;
END;

已尝试的解决方案（均替换原代码中的set serveroutput on;行） #

• 方案1：

set serveroutput on size unlimited

• 方案2：

dbms_output.enable(NULL)

• 方案3：

SET SERVEROUTPUT ON size '10000000'

DBMS_OUTPUT.ENABLE(10000000);

• 方案4：

SET SERVEROUTPUT ON size 10000000

DBMS_OUTPUT.ENABLE(10000000);

完整报错信息 #

Error starting at line : 3 in command -
DECLARE
    c_id    t_person.id%TYPE;
    c_value t_person_communication_method.value%TYPE;
    c_comm_id t_person_communication_method.id%TYPE;
    CURSOR cr IS
 SELECT
        fppc.id as id, fppc.value as value, fppc.person_id as person_id
    FROM
        (
            SELECT
                ppc.*,
                ROW_NUMBER()
                OVER(PARTITION BY ppc.value
                     ORDER BY
                         ppc.value
                ) rn
            FROM
                (
                    SELECT
                        cm.id,
                        cm.person_id,
                        p.first_name,
                        p.last_name,
                        p.business_flow_id,
                        cm.type,
                        cm.value,
                        cm.is_verified,
                        cm.created_date,
                        cm.created_channel
                    FROM
                             t_person p
                        JOIN t_person_communication_method cm ON p.id = cm.person_id
                    WHERE
                           cm.type = 'EMAIL'
                        AND cm.is_verified = 'Y'
                    ORDER BY
                        p.id ASC
                ) ppc
        ) fppc
    WHERE
        fppc.rn > 1;

BEGIN
    OPEN cr;
    LOOP
       update t_person_login set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person_login_history set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person set first_name ='anonymous',last_name ='anonymous' where id = c_id;
        update t_person_communication_method set value ='anonymous@gmail.com' where type = 'EMAIL' and person_id =c_id;
        update t_person_communication_method set value ='00000000000' where type = 'PHONE' and person_id =c_id and id=c_comm_id;
        update t_person_comm_method_history set value = 'anonymous@gmail.com' where type ='EMAIL' and person_id =c_id;
        update t_person_address set postcode ='anonymous',address_1 ='anonymous',city='anonymous',county ='anonymous' where person_id =c_id;        
        EXIT WHEN cr%notfound;
        dbms_output.put_line(c_id || ' ' || c_value || '' || c_comm_id);
    END LOOP;

    CLOSE cr;
END;
Error report -
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 54
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

问题分析与修复 #

核心错误 #

1. 游标循环未读取数据，导致死循环：仅打开游标但未通过FETCH语句将数据读取到变量中，cr%notfound永远为false，循环无限执行。
2. 变量未赋值，update语句无效：c_id、c_comm_id等变量从未被赋值，初始值为NULL，所有update语句的where条件因NULL比较永远不成立，不会更新任何数据，但循环持续执行。
3. 输出逻辑无限触发：死循环中不断执行dbms_output.put_line，输出未初始化的变量值，导致输出缓冲区持续被填充，最终触发溢出错误——这才是问题根源，而非缓冲区大小设置不足。

修复后的代码 #

set serveroutput on size unlimited;

DECLARE
    c_id        t_person.id%TYPE;
    c_value     t_person_communication_method.value%TYPE;
    c_comm_id   t_person_communication_method.id%TYPE;
    CURSOR cr IS
 SELECT
        fppc.id as id, fppc.value as value, fppc.person_id as person_id
    FROM
        (
            SELECT
                ppc.*,
                ROW_NUMBER()
                OVER(PARTITION BY ppc.value
                     ORDER BY
                         ppc.value
                ) rn
            FROM
                (
                    SELECT
                        cm.id,
                        cm.person_id,
                        p.first_name,
                        p.last_name,
                        p.business_flow_id,
                        cm.type,
                        cm.value,
                        cm.is_verified,
                        cm.created_date,
                        cm.created_channel
                    FROM
                             t_person p
                        JOIN t_person_communication_method cm ON p.id = cm.person_id
                    WHERE
                           cm.type = 'EMAIL'
                        AND cm.is_verified = 'Y'
                    ORDER BY
                        p.id ASC
                ) ppc
        ) fppc
    WHERE
        fppc.rn > 1;

BEGIN
    OPEN cr;
    LOOP
        -- 先读取游标数据到变量，再判断是否退出
        FETCH cr INTO c_comm_id, c_value, c_id;
        EXIT WHEN cr%notfound;
        
        -- 执行更新操作
        update t_person_login set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person_login_history set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person set first_name ='anonymous',last_name ='anonymous' where id = c_id;
        update t_person_communication_method set value ='anonymous@gmail.com' where type = 'EMAIL' and person_id =c_id;
        update t_person_communication_method set value ='00000000000' where type = 'PHONE' and person_id =c_id and id=c_comm_id;
        update t_person_comm_method_history set value = 'anonymous@gmail.com' where type ='EMAIL' and person_id =c_id;
        update t_person_address set postcode ='anonymous',address_1 ='anonymous',city='anonymous',county ='anonymous' where person_id =c_id;        
        
        dbms_output.put_line(c_id || ' ' || c_value || ' ' || c_comm_id);
    END LOOP;

    CLOSE cr;
    COMMIT; -- 建议添加提交语句，确保更新生效
END;

修复说明 #

• 添加FETCH cr INTO c_comm_id, c_value, c_id;：将游标返回的id（通信方式ID）、value、person_id分别赋值给对应变量，保证变量有有效值。
• 调整EXIT WHEN位置：先读取数据再判断是否退出，避免最后一次循环执行无效更新。
• 添加COMMIT：确保所有更新操作提交到数据库（根据业务需求调整，若不需要自动提交可移除）。

内容的提问来源于stack exchange，提问作者Dulani Maheshi