ORA-04030: out of process memory when trying to allocate 152 bytes (Logminer LCR c,krvtadc)

  今天使用LogMiner找回誤更新的數據時，查詢v$logmnr_contents時，遇到了“ORA-04030: out of process memory when trying to allocate 152 bytes (Logminer LCR c,krvtadc)”錯誤。查了一下My Oracle Support，發現出現ORA-04030錯誤的原因，特摘錄在此。

   如果指定了COMMITTED_DATA_ONLY選項而且發出了查詢，則LogMiner會在記憶體中的單個事務中逐步重做所有重做記錄, 直到 logminer 找到該事務的提交記錄。 因此，可能會耗盡記憶體，在這種情況下將出現“Out of Memory”錯誤。具體參考下麵資料：

ORA-04030: out of process memory when trying to allocate 152 bytes (Logminer LCR c,krvtadc)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 12.1.0.1 [Release 10.2 to 12.1]
Information in this document applies to any platform.
This problem can occur on any platform.

SYMPTOMS

Select against logminer fails with ORA-4030 (Logminer LCR c,krvtadc).

Following query failed:
SELECT username, TO_CHAR(timestamp,'HH24:MI:SS'), substr(sql_redo,1,110)
FROM v$logmnr_contents
WHERE seg_owner='<owner name>'
AND seg_name IN ('<table name>');

FROM v$logmnr_contents where
ERROR at line 2:
ORA-4030: out of process memory when trying to allocate 56 bytes (Logminer LCR c,krvtadc)

CAUSE

According to the documentation in Oracle® Database Utilities:
If the COMMITTED_DATA_ONLY option is specified and you issue a query, LogMiner stages all redo records within a single transaction in memory until LogMiner finds the commit record for that transaction. Therefore, it is possible to exhaust memory, in which case an "Out of Memory" error will be returned.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '<filename>',
options => DBMS_LOGMNR.NEW);
EXECUTE sys.DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '<dictionary file>',
options =>dbms_logmnr.committed_data_only);

SOLUTION

If this occurs, you must restart LogMiner without the COMMITTED_DATA_ONLY option specified and reissue the query.

REFERENCES

參考資料：

   Select Against Logminer Fails With ORA-4030 (Logminer LCR c,krvtadc) (文檔 ID 807080.1)