How do I limit Oracle's memory use?
The problem was that when the database was installed, conflicting other parameters were set. So I needed to export those from the "spfile" to a "pfile", back them up, edit them, test them and import from the pfile to the spfile:
SQL> create pfile='some/file/path' from spfile;
File created.
Then I set the conflicting parameters to zero:
...
--*.pga_aggregate_target=3270m
*.pga_aggregate_target=0m
...
--*.sga_target=9811m
*.sga_target=0m
...
I then try starting up again:
SQL> startup pfile='some/file/path';
ORACLE instance started.
...
And check the memory_target and memory_max_target parameters as above. Once happy with things, to keep the settings I did:
SQL> create spfile from pfile='some/file/path';
File created.
To answer your original question in title:
Starting with 12c, one can easily limit both SGA and PGA. SGA could be limited in earlier releases also by sga_max_size
or other parameters from which the database calculated sga_max_size
.
The problematic part was usually the PGA, with no simple limit value just some workarounds, but that can be now easily limited by using the pga_aggregate_limit
parameter.