Skip to Main Content
  • Questions

  • The non-default value for UNDO_RETENTION is not recorded in SPFILE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: January 15, 2020 - 7:42 pm UTC

Last updated: January 20, 2020 - 3:03 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

I have modified the value for UNDO_RETENTION by using:

alter system set undo_retention=28800 scope=both sid='*';


Then I create PFILE from SPFILE. The UNDO_RETENTION parameter is not present in the PFILE. If I connect to database and do a show parameter undo_retention, I see 28800. So, why is this value not recorded in SPFILE?

Upon querying the v$parameter view, the ISDEFAULT=TRUE for this parameter. This is also not right as the parameter has been modified from it's default value of 900. The ISMODIFIED column shows SYSTEM_MOD. The v$spparameter shows ISSPECIFIED=TRUE. Is this expected behavior? I cannot find anything in documentation or MOS.

Thanks...

and Connor said...

Are you sure you are looking at the right spfile? Because I can't reproduce that on my 11g instance

Before

UNDO_RET_BEFORE

After

UNDO_RET_AFTER

Rating

  (1 rating)

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

Comments

A reader, January 17, 2020 - 2:42 pm UTC

Thanks Connor. I am so sorry for wasting your time. Yes, I was looking at the wrong file. I guess being able to have a second set of eyes take a look is what makes this forum so valuable.

Connor McDonald
January 20, 2020 - 3:03 am UTC

Glad we could help.

Of course, here at AskTOM, we've never made any mistakes...LOL (just kidding)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database