-1

I'd like to know whether there is some way to know the undo_retention needed for a specified query with long duration that need keep the consistence.

In other words, I'd like to know how long my query will be running.

  • @Michael Green "In other words, I'd like to know how long my query will be running." Thus may be an answer but it is not "in other words" and it is not clear if the OP is aware of this. Besides that, it is wrong.So I think it is not a good idea to add this comment to the question and irovode a reason for a close as too localized, – miracle173 Nov 15 at 23:02
  • @miracle173 Not my words. They are another's comment-question and the OP's comment-response. I thought that information was better located in the body of the question rather than in ancillary comments. I notice both comments have been removed since. You're welcome to further edit the question if you believe you can improve it. – Michael Green Nov 17 at 9:45
  • Neither did I vote to close. Most of those who did frequent chat if you'd like to engage with them to better understand their motivation. – Michael Green Nov 17 at 9:46
  • @MichaelGreen Moving the OP's comments to the question is ok. But this wasn't a comment of the OP. But when I read this comment I didn't see OP's response to this comment. So I assume now you were right to move this comment in the question's body. Unfortunately the comments were already deleted. – miracle173 Nov 17 at 10:10
1

The undo_retention needed is going to depend on how long the query runs for, how much undo space it will need, how much undo space is being used by the database among other things. Also depending on how the undo space is setup it may be set automaticly. Because of all of this there is no simple answer that you will need an undo_retention of X for a given query.

Oracle 12c undo

The undo retention period is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it.

The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size. See "Automatic Tuning of Undo Retention" for more information.

  • how does the undo_retention needed depend on how much undo space it will need and how does the undo_retention needed depend on how much undo space is being used by the database ? – miracle173 Nov 15 at 23:24
  • @miracle173 if you have a large query that uses 75% of the undo space and after it is done another large query comes along wouldn't that cause the previous query to get over written sooner? – Joe W Nov 16 at 0:00
  • and in this situation you will change the undo_retention parameter? – miracle173 Nov 16 at 7:06
  • @miracle173 I never said it would. Also oracle even states that based on certain parameters the undo_retention parameter will be ignored in the first place. It still is the minimum amount of time it attempts to retain the information which means the period can be much shorter in some cases. – Joe W Nov 16 at 15:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.