I would like to use Oracle's automatic memory management, with a limit of around 4GB. (Past experience has shown this to be plenty for my dev PC and that it is easier to get an idea about any performance issues with less than that.)
I believe these are the instructions to follow:
It says in short, to enable automatic memory management, set memory_target and (optionally) memory_max_target. The former parameter should be a dynamic value and the latter a harder limit, that can only be changed when stopping/starting the database.
I set these as follows as sysdba:
SQL> alter system set memory_target = 4G scope = spfile; System altered. SQL> alter system set memory_max_target = 8G scope = spfile; System altered.
and check the values with
SQL> show parameter target;
Scope can be just "memory" for the current uptime, "spfile" or "both"; I pick the spfile as I will be restarting:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup
That, however, was too simplistic:
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 13104M
It is not hard to revert, but in my opinion also not immediately obvious how to get the memory adjustment through, so I will post what I did below and the senior posters can correct/shred me. In either case I think it is nice to have it in one place here.