Skip to Main Content
  • Questions

  • Lots of archivelog generation when shrinking and compacting segments

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jalpan.

Asked: May 11, 2009 - 9:14 am UTC

Last updated: September 29, 2017 - 1:04 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

1. what is the reason of huge redo and archivelog generation when compacting and shriking huge segments in 10g?

2. How it can be avoided or minimized?

Thanks
JP

and Tom said...

1) you are moving lots of data, that movement has to be "repeatable" in the event you need to recovery from a media failure. Also, consider that the rowids change when you shrink space compact - which means the indexes get updated in addition to the tables

2) it cannot be, it is what it is. The movements have to be recorded so that we can recover them in the event of a failure of media or just a power down.

Rating

  (3 ratings)

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

Comments

Jalpan Pota, May 13, 2009 - 7:08 am UTC


Do we have any change in 11g for redo generation?

Rahul, September 28, 2017 - 2:58 am UTC

We do shrinking of tables monthly. This activity generates lot of redo and our archive gets full. Do we have any enhancements for shrink in 11g so that we can minimize the Redo logs?
Connor McDonald
September 29, 2017 - 1:04 pm UTC

Not to my knowledge.

The question I'd be asking is - do you have metrics that prove the benefit of doing the shrink.

Because if you do not...then it might be wasted activity

A reader, September 30, 2017 - 4:40 am UTC

Hello Rahul, Which script or command you fire for shrink operation can you post here. and SG what? is country or company?

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.