I don't know if this is documented or is an abnormal functioning of the pga aggregate limit. According to my tests (see below) done both in 19c and 23c, it turns out that when logged into a root container, with a user (different from SYS) we are not concerned by the limit imposed via the pga_aggregate_limit. This is also the case even when we alter session to change the container from the root cdb to the pbd. The pga limit seems to be imposed only if we are connected directly to the pdb.
-- First test done in the root container
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "C##MHOURI"
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ---------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 600M
SQL> @mysid
SID
----------
237
DECLARE
TYPE v1_type IS
TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
v1 v1_type;
BEGIN
FOR i IN 1..130000 LOOP
v1(i) := rpad('x', 32767);
END LOOP;
END;
/
PL/SQL procedure successfully completed. --> no error despite the 5GB of pga used
SQL> @getpgaSID 237
Display session 237 memory usage from v$process_memory....
SID PID SERIAL# CATEGORY PGA_ALLOC_GB PGA_USED_GB PGA_MAX_ALLOC_GB CON_ID
---- ---- ---------- --------- ------------ ----------- ---------------- ----------
237 35 4 Other .01 .01 1
237 35 4 PL/SQL 5.08 4.95 5.08 1
237 35 4 SQL 0 0 0 1
Now, I am going to repeat the same thing but I will switch to the pdb container
SQL> alter session set container=ORCLPDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1
SQL> show user
USER is "C##MHOURI"
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 600M
SQL> @mysid
SID
----------
237
DECLARE
TYPE v1_type IS
TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
v1 v1_type;
BEGIN
FOR i IN 1..130000 LOOP
v1(i) := rpad('x', 32767);
END LOOP;
END;
/
PL/SQL procedure successfully completed. -> no error despite the 5GB of pga used
SQL> @getpgaSID 237
Display session 237 memory usage from v$process_memory....
SID PID SERIAL# CATEGORY PGA_ALLOC_GB PGA_USED_GB PGA_MAX_ALLOC_GB CON_ID
---- ---- ---------- -------- ------------ ----------- ---------------- ----------
237 35 6 Other .04 .04 3
237 35 6 PL/SQL 4.78 4.65 4.78 3
237 35 6 SQL 0 0 0 3
Finally, I'm going to do the same thing again, but this time I will firt connect directly to the pdb.
[oracle@localhost xyz]$ sqlplus c##mhouri/mhouri@localhost:1521/ORCLPDB1
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1
SQL> show user
USER is "C##MHOURI"
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- --------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 600M
SQL> @mysid
SID
----------
237
DECLARE
TYPE v1_type IS
TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
v1 v1_type;
BEGIN
FOR i IN 1..130000 LOOP
v1(i) := rpad('x', 32767);
END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
SQL> @getpgaSID 237
Display session 237 memory usage from v$process_memory....
SID PID SERIAL# CATEGORY PGA_ALLOC_GB PGA_USED_GB PGA_MAX_ALLOC_GB CON_ID
---- ---- ---------- --------- ------------ ----------- ---------------- ----------
237 35 7 Other .03 .03 3
237 35 7 PL/SQL 0 0 1.68 3
237 35 7 SQL 0 0 0 3
Show Active workarea memory usage for where sid=237...
In short, it's understandable not to perform heavy processing from the root container, but it would have been acceptable if this limit had been imposed boot in root and pdb containers.
Best regards
Mohamed Houri