expdp報ORA-04030 out of process memory錯誤

作業系統：AIX 6.1
Oracle資料庫版本：11.1.0.7.0
     1、使用expdp匯出的時候， 報ORA-04030: out of process memory when trying to allocate 120048 bytes錯誤，
           具體如下所示：

ORA-39082: Object type ALTER_PROCEDURE:"INDEXAPP"."PA_INDEX_PORTAL_UV_D" created with compilation warningsProcessing object type SCHEMA_EXPORT/VIEW/VIEWORA-39082: Object type VIEW:"INDEXAPP"."TA_CONTENT_AUDIT_USER_D" created with compilation warningsProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS##ORA-39014: One or more workers have prematurely exited.ORA-39029: worker 1 with process name "DW01" prematurely terminatedORA-31671: Worker process DW01 had an unhandled exception.ORA-04030: out of process memory when trying to allocate 120048 bytes (session heap,kuxLpxAlloc)ORA-06512: at "SYS.KUPW$WORKER", line 1602ORA-06512: at line 2

     2、以oracle使用者執行ulimit -a，對於使用者進程使用的記憶體是有限制的，如下所示：

$ ulimit -atime(seconds)        unlimitedfile(blocks)         unlimiteddata(kbytes)         256000stack(kbytes)        256000memory(kbytes)       256000coredump(blocks)     unlimitednofiles(descriptors) 2000threads(per process) unlimitedprocesses(per user)  unlimited

      3、切換到root，在/etc/security/limits檔案添加如下內容，(-1代表不限制):

oracle:data = -1stack_hard = -1stack = -1rss = -1core = -1nofiles = -1

      4、查看新增以上內容後的/etc/security/limits檔案:

$ su - rootroot's Password:# cat /etc/security/limits** Sizes are in multiples of 512 byte blocks, CPU time is in seconds** fsize      - soft file size in blocks* core       - soft core file size in blocks* cpu        - soft per process CPU time limit in seconds* data       - soft data segment size in blocks* stack      - soft stack segment size in blocks* rss        - soft real memory usage in blocks* nofiles    - soft file descriptor limit* fsize_hard - hard file size in blocks* core_hard  - hard core file size in blocks* cpu_hard   - hard per process CPU time limit in seconds* data_hard  - hard data segment size in blocks* stack_hard - hard stack segment size in blocks* rss_hard   - hard real memory usage in blocks* nofiles_hard - hard file descriptor limit** The following table contains the default hard values if the* hard values are not explicitly defined:**   Attribute        Value*   ==========    ============*   fsize_hard    set to fsize*   cpu_hard      set to cpu*   core_hard         -1*   data_hard         -1*   stack_hard      8388608*   rss_hard          -1*   nofiles_hard      -1** NOTE:  A value of -1 implies "unlimited"*default:fsize = -1core = -1cpu = -1data = 512000rss = 512000stack = 512000nofiles = 2000root:data = -1stack_hard = -1stack = -1rss = -1core = -1nofiles = -1oracle:data = -1stack_hard = -1stack = -1rss = -1core = -1nofiles = -1daemon:bin:sys:adm:uucp:guest:nobody:lpd:pconsole:stack_hard = 131072data = 1280000data_hard = 1280000esaadmin:stack = 393216stack_hard = 393216

          5、切換到oracle使用者，執行ulimit -a，結果顯示如下：

# su - oracle$ ulimit -atime(seconds)        unlimitedfile(blocks)         unlimiteddata(kbytes)         unlimitedstack(kbytes)        unlimitedmemory(kbytes)       unlimitedcoredump(blocks)     unlimitednofiles(descriptors) unlimitedthreads(per process) unlimitedprocesses(per user)  unlimited

     6、再次執行expdp匯出，仍舊報一樣的錯誤
     7、重啟作業系統後，參數生效，匯出不再報錯

本文出自 “斜陽悠悠寸草心” 部落格，請務必保留此出處http://fly1116.blog.51cto.com/8301004/1339098