Oracle Memory Configuration – How to Limit Oracle’s Memory Use

configurationmemoryoracle-12c

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:

Oracle 12 automatic memory management

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.

Best Answer

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.
Related Question