1] Use session statistics (to measure)

The Oracle server maintains statistics that record the PGA and UGA memory consumption on a per session basis. If you suspect a memory leak then use SQL similar to that presented below to verify the amount of memory in use and re-issue it several times and monitor to confirm that memory is indeed growing.
sql>
select sid, substr(name,1,30), value , value/(1024*1024) "MB"
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and name like '%a memory'
order by sid, name;


Note that if you do not see one of these statistics increase yet the process still seems to increase in memory consumption from the operating system standpoint then perhaps the problem lies with the your application code where memory is allocated but not released/

2] You may also experience ora-4031 / ora-4030 errors running your sql statements. Please note that getting these errors does not automatically indicate that you have a memory leak, because you may not have configured your system appropriately. Remember that old l0ogic truth - a bus is a motor vehicle, but not all motor vehicles are buses.

--additional Info


select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", c.serial#,c.status,c.username,c.schemaname,c.osuser,c.machine,c.terminal,c.program,c.module,state,logon_time
from v$statname a, v$sesstat b,v$session c
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=c.sid
and osuser!='oracle'
order by status,MB desc,sid, name;


If you want to the SQL also join v$sqlarea table- This will give you how much memory is utilized by package/procedure and a simple/complex SQL text

select b.sid, substr(name,1,30), value , value/(1024*1024) "MB", s.serial#,s.sid ssid, s.status,s.username,s.schemaname,s.osuser,s.machine,s.terminal,s.program,s.module,state,logon_time,substr(p.spid,1,8) spid, substr(sa.sql_text,1,2000) txt
from v$statname a, v$sesstat b,v$session s, v$process p ,v$sqlarea sa
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=s.sid
and osuser!='oracle'
and p.addr = s.paddr
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
order by status,MB desc,sid, name;

conn "sys/pass as sysdba"
show parameter log_archive_dest
alter system set log_archive_dest_1="new loc" or
alter system set db_recovery_file_dest="new loc";
alter system set log_archive_dest_1='location=/backup/DUPRPTDB/archive';
alter system switch logfile

select '&1' from &2;
exit 5;