PGA_AGGREGATE_TARGET is not a hard limit. Lather, rinse, and repeat 🙂
I like to think of it as a tuning suggestion to Oracle as to how much it can allocate for things such as sorts and hash tables. Below is a test case that shows this.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 788M
SQL> declare
2 type t_array is table of varchar2(4000);
3 l_array t_array := t_array();
4 l_mem number;
5 l_spid number;
6 begin
7 for i in 1..100000 loop
8 l_array.extend(1);
9 l_array(i) := rpad('.',3900,'.');
10 end loop;
11 dbms_lock.sleep(5);
12 select pga_alloc_mem into l_mem from v$process where addr = (select paddr from v$session where sid = sys_context('userenv','sid'));
13 dbms_output.put_line((trunc(l_mem / 1024 / 1024)) || 'MB used before clearing the array.');
14 l_array.delete;
15 dbms_session.free_unused_user_memory();
16 dbms_lock.sleep(5);
17 select pga_alloc_mem into l_mem from v$process where addr = (select paddr from v$session where sid = sys_context('userenv','sid'));
18 dbms_output.put_line((trunc(l_mem / 1024 / 1024)) || 'MB used after clearing the array.');
19 end;
20 /
1393MB used before clearing the array.
699MB used after clearing the array.
PL/SQL procedure successfully completed.
SQL> select trunc(pga_alloc_mem / 1024 / 1024) from v$process where addr = (select paddr from v$session where sid = sys_context('userenv','sid'));
TRUNC(PGA_ALLOC_MEM/1024/1024)
------------------------------
4
SQL>
Notice we allocated almost 1.4GB of memory, which almost doubled what has been configured as our target. Nothing prevents a developer from loading up a huge array and bringing your database server to its knees…other than testing, of course 🙂