Skip to Main Content
  • Questions
  • ORA-00838: Specified value of MEMORY_TARGET is too small

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

Asked: April 06, 2017 - 8:04 pm UTC

Last updated: April 18, 2023 - 3:22 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I have a oracle 12c installation.

Following commands were executed as SYS user.

ALTER SYSTEM SET MEMORY_MAX_TARGET=20G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_TARGET = 20G SCOPE = SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 15G SCOPE = SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 20G SCOPE = SPFILE;

Database was shutdown.

Database was restarted.

We end up with an error

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 22912M

Now either we cannot stop the database nor correct the MEMEORY_TARGET. We end up with

ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

How do we recover from this?

Thanks!

and Connor said...

If your instance is down, you can still adjust parameters by temporarily making a static pfile, eg

C:\oracle\>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 7 13:57:50 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> create pfile = 'c:\temp\init.ora' from spfile;

File created.



Then you edit the file and use that to start, ie

startup pfile=c:\temp\init.ora

and then, once your database is open

create spfile from pfile=c:\temp\init.ora




Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

Ravi B, April 07, 2017 - 6:03 am UTC

Thanks. That works!

Excellent recommendation!

RR, March 22, 2018 - 4:01 pm UTC

It works for me too, great post, thanks a lot!

Muralidharan Raman, July 20, 2018 - 2:10 pm UTC

Thanks a lot... it worked for me too, its a great help
Connor McDonald
July 21, 2018 - 6:34 am UTC

glad we could help

Ezhilvendhan, November 28, 2018 - 9:25 am UTC

It works well. Thank you so much Tom!!
Connor McDonald
November 29, 2018 - 12:30 am UTC

glad we could help

OUTDATED???

Θωμάς Παραβαΐτσης, April 08, 2019 - 3:35 pm UTC

Maybe the answer is updated because when I try to do that now I get those after startup pfile=c:\oraclexe\init.ora, i can create the file but after that, i get this again ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 2048M
ORA-01078: failure in processing system parameters
Connor McDonald
April 09, 2019 - 1:36 am UTC

Yes, but the point here was that you can then *edit* the pfile you created to correct any of the errors you have. Then once your instance is started, you can create an spfile out of that pfile.

Thank you it worked for me.

A reader, July 10, 2019 - 4:27 pm UTC


Chris Saxon
July 11, 2019 - 2:52 pm UTC

Glad this helped.

Parvaz, November 23, 2019 - 12:15 pm UTC

Thank you for the solution! It works!

i had to add quote around the file location while creating pfile from the spfile.

ie: create spfile from pfile='c:\temp\init.ora'
Connor McDonald
November 25, 2019 - 12:22 pm UTC

glad we could help

Munir, February 22, 2020 - 6:55 pm UTC

Thank you for the solution!
Connor McDonald
February 25, 2020 - 8:16 am UTC

Glad we could help

A reader, October 20, 2020 - 3:28 pm UTC

thnaks it wors

A reader, November 11, 2020 - 4:27 pm UTC

It's really a good solution

Thanks

Odunfa Oluwagbenga, January 14, 2021 - 12:52 pm UTC

Thanks that works!

thanks

santiago, January 15, 2021 - 4:46 pm UTC

thanks, works so good, but. In linux must includ all spfile route and the (') character.

example:

create spfile='/u01/app/oracle/product/version/dbs/spfilename.ora' from pfile='/u01/app/oracle/product/version/dbs/init.ora'

Thank it work

Naeem Zayid, January 19, 2021 - 8:10 pm UTC

Thank it work with me, the point is to edit the init,ora file while DB is down, then I edit the file
..
xe.__data_transfer_cache_size=0
xe.__db_cache_size=0
xe.__inmemory_ext_roarea=0
xe.__inmemory_ext_rwarea=0
xe.__java_pool_size=0
xe.__large_pool_size=0
.
.

.
.*.memory_target=714572800
,

than I run

startup pfile=c:\temp\init.ora

Thank a lot

Connor McDonald
January 20, 2021 - 5:58 am UTC

Glad it worked out

A reader, February 09, 2021 - 2:57 pm UTC

I edited the file and when I restart, it gives me the message:
ORA-56752: Oracle Database Express Edition (XE) memory parameter invalid or not specified
Connor McDonald
February 10, 2021 - 4:59 am UTC

Edited what file? What error? etc etc.

Help us to help you

Thank you!! this helped a lot

A reader, May 05, 2022 - 4:45 am UTC

Thank you so much! Connor!
Connor McDonald
May 11, 2022 - 5:33 am UTC

Happy to help!

A reader, January 18, 2023 - 5:16 pm UTC

Excellent ! very helpful thanx !

Worked like magic, thanks

Bukky, April 14, 2023 - 11:43 pm UTC

I had the same issue "Specified value of MEMORY_TARGET is too small, needs to be at least 3016M ORA-01078: failure in processing system parameters"

Steps carried out;
create pfile = 'init.ora' from spfile
create spfile from pfile = 'init.ora'
startup

Thanks a lot.
Connor McDonald
April 18, 2023 - 3:22 am UTC

Glad we could help