Script:计算Oracle Streams进程所占用的内存大小

以下脚本可以用于收集Oracle Streams进程的内存使用信息， 便于诊断因memory leak引起的内存问题:

ps auxgw | sort  -n +5 | tail -10

select a.apply_name,
       a.server_id,
       b.sid,
       b.program,
       c.spid,
       trunc(c.pga_used_mem / (1024 * 1024), 2) PGA_USED,
       trunc(c.pga_alloc_mem / (1024 * 1024), 2) PGA_ALLOC,
       trunc(c.pga_freeable_mem / (1024 * 1024), 2) PGA_FREE,
       trunc(c.pga_max_mem / (1024 * 1024), 2) PGA_MAX
  from v$streams_apply_server a, v$process c, v$session b
 where a.sid = b.sid
   and b.paddr = c.addr
 order by 1, 2;

SELECT p.spid spid,
       'C00' || c.capture# || ' ' || upper(lp.role) as process,
       c.capture_name "Capture Name",
       s.sid,
       s.program,
       trunc(p.pga_used_mem / (1024 * 1024), 2) PGA_USED,
       trunc(p.pga_alloc_mem / (1024 * 1024), 2) PGA_ALLOC,
       trunc(p.pga_freeable_mem / (1024 * 1024), 2) PGA_FREE,
       trunc(p.pga_max_mem / (1024 * 1024), 2) PGA_MAX
  FROM v$streams_capture c, v$logmnr_process lp, v$session s, v$process p
 WHERE c.logminer_id = lp.session_id
   AND lp.role in ('reader', 'preparer', 'builder')
   AND lp.sid = s.sid
   AND lp.serial# = s.serial#
   AND s.paddr = p.addr
 order by capture_name, process;

sqlplus / as sysdba
oradebug setospid $PID
oradebug unlimit
oradebug dump heapdump 536870917
oradebug tracefile_name