Skip to Main Content
  • Questions

  • How to calculate UNDO size for SHRINK

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Denis.

Asked: July 28, 2016 - 5:34 pm UTC

Last updated: August 01, 2016 - 7:37 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hello!
I have an issue. I need to reclaim unused space. I have found that MOVE is not an option for me, because table is large (55 GB), have some indexes and it should be done online.

So I tried to use SHRINK SPACE after performing some cleanup. But got an error:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'.

At the moment UNDO tablespace is not autoextensible and sized to 4 GB. Theoretically DBA can extend it or make it autoextensible. But I want to understand how to handle cases like this in the future.

So my questions are:
How to calculate approx size of UNDO tablespace that required for Oracle to make a shrink of some table?
How does SHRINK work?

I understand what shrink should do, but not sure how. For example I have 3 GB of real data in tablespace and 52 GB unused. UNDO - 4 GB.
Will it be enough for Oralce to move rows between files to complete SHRINNK successfully? Per my understanding, Oracle should move each row to the beginning of some file. But will Oracle do it only one time for each row, placing information to UNDO only once? Or multiple times?

Thanks in advance.

and Connor said...

Conceptually it is similar to a series of delete and inserts on a row by row level.

Of course, deleting 1 row on a table with 'n' indexes, will consume much more undo than the same delete on a table with no indexes. So 3G of "real data" (being 3G of rows) could easily consume 3G x 'n' for each delete, and then more for the inserts.

(This of course depends on how much of the 3G will actually be moved).

But you could probably approximate the task by creating a small table with the same type of data and indexes, and checking the USED_... columns in v$transaction after doing some sample single row deletes and inserts.

Hope this helps.


Rating

  (1 rating)

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

Comments

Denis Kurashko, August 01, 2016 - 8:29 am UTC

Hi!
Thanks for your answer. It makes sense!
Cheers
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.