oracle 过程缓冲区溢出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7971862/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Procedure Buffer overflow
提问by x.509
I have following procedure which is filling up null values in a column. The procedure works fine if i have very small set of data. But data that i am targettign on is about 3 billions of record. Just having this script tested on 1 Million record threw these execptions.
我有以下过程，用于填充列中的空值。如果我的数据集非常小，则该过程可以正常工作。但是我所针对的数据大约有 30 亿条记录。仅仅在 100 万条记录上测试这个脚本就会引发这些 execpions。
ORA-20000: ORU-10027: buffer overflow, limit of 20000 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 "DBNAME.PRBACKFILLI", line 39
ORA-06512: at line 2
After having a little bit digging, I realized that DBMS_OUTPUT.PUT_LINEprints output at the end of the procedure. Now the thing is we want debugging info, what should we do?
经过一些挖掘，我意识到DBMS_OUTPUT.PUT_LINE在程序结束时打印输出。现在的问题是我们想要调试信息，我们应该怎么做？
CREATE OR REPLACE PROCEDURE PRBACKFILL (str_dest IN VARCHAR2) AS
CURSOR cr_pst_ IS
select id, seq from TABLE_ where ID is null;
TYPE t_id_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE t_seq_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
a_id t_id_array;
a_seq t_seq_array;
i_bulk_limit NUMBER := 1000;
BEGIN
OPEN cr_pst_;
LOOP
FETCH cr_pst_
BULK COLLECT INTO a_id, a_seq LIMIT i_bulk_limit;
FOR i IN 1..a_id.count LOOP
a_id(i) := Floor(a_seq(i)/10000000000000);
END LOOP;
FORALL i IN 1 .. a_id.count
UPDATE TABLE_
SET ID = a_id(i)
WHERE SEQ = a_seq(i);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('COMMITED '||i_bulk_limit||' records');
EXIT WHEN cr_pst_%NOTFOUND;
END LOOP; -- main cursor loop
CLOSE cr_pst_;
DBMS_OUTPUT.PUT_LINE ('Backfill completed gracefully!');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No more records to process');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('errno: '||TO_CHAR(SQLCODE)||' Msg: ' || SQLERRM);
END PRBACKFILL;
.
/
sho err;
回答by Justin Cave
First off, you would normally not use DBMS_OUTPUTfor logging. It would generally make far more sense to write the data to a log table particularly if your logging procedure was defined as an autonomous transaction so that you could monitor the log data while the procedure was running. DBMS_OUTPUTis only going to be displayed after the entire procedure has finished executing at which point it's generally somewhat pointless.
首先，您通常不会DBMS_OUTPUT用于日志记录。将数据写入日志表通常会更有意义，特别是如果您的日志记录过程被定义为自主事务，以便您可以在过程运行时监视日志数据。 DBMS_OUTPUT只有在整个过程完成执行后才会显示，此时它通常有点毫无意义。
Related to that first point, relying on DBMS_OUTPUTto indicate to the caller that there has been some sort of exception is a very poor practice. At a minimum, you'd want to re-raise the exception that was thrown so that you'd get the error stack in order to debug the problem.
与第一点相关，依赖于DBMS_OUTPUT向调用者表明存在某种异常是一种非常糟糕的做法。至少，您需要重新引发抛出的异常，以便获得错误堆栈以调试问题。
Second, when you enable output, you have to specify the size of the buffer that DBMS_OUTPUTcan write to. It appears that you've declared the buffer to be 20,000 bytes which is the default if you simply
其次，当您启用输出时，您必须指定DBMS_OUTPUT可以写入的缓冲区的大小。看来您已将缓冲区声明为 20,000 字节，这是默认值，如果您只是
SQL> set serveroutput on;
You can change that by specifying a size but the maximum size is limited to 1,000,000 bytes
您可以通过指定大小来更改它，但最大大小限制为 1,000,000 字节
SQL> set serveroutput on size 1000000;
If you plan on updating 3 billion rows in 1000 row chunks, that's going to be far too small a buffer. You're going to generate more than 60 times that amount of data with your current code. If you are using 10.2 both on the client and on the server, you should be able to allocate an unlimited buffer
如果您计划在 1000 个行块中更新 30 亿行，那么缓冲区就太小了。您将使用当前代码生成超过 60 倍的数据量。如果您在客户端和服务器上都使用 10.2，您应该能够分配无限缓冲区
SQL> set serveroutput on size unlimited;
but that is not an option in earlier releases.
但这不是早期版本中的一个选项。
Finally, are you certain that you need to resort to PL/SQL in the first place? It appears that you could do this more efficiently by simply executing a single UPDATE
最后，您确定首先需要求助于 PL/SQL 吗？看起来你可以通过简单地执行一个 UPDATE 来更有效地做到这一点
UPDATE table_
SET id = floor( seq/ 10000000000000 )
WHERE id is null;
That's much less code, much easier to read, and will be more efficient than the PL/SQL alternative. The only downside is that it requires that your UNDO tablespace be large enough to accommodate the UNDO that is generated but updating a single column from NULL to a non-NULL numeric value shouldn't generate that much UNDO.
那是更少的代码，更容易阅读，并且比 PL/SQL 替代方案更有效。唯一的缺点是它要求您的 UNDO 表空间足够大以容纳生成的 UNDO，但是将单个列从 NULL 更新为非 NULL 数值不应该生成那么多的 UNDO。
