Skip to Main Content
  • Questions
  • Reorganization Within Same Tablespace and To New Tablespace

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tharmendran.

Asked: September 08, 2021 - 3:13 am UTC

Last updated: September 13, 2021 - 1:57 am UTC

Version: Oracle 19c

Viewed 100+ times

You Asked

Hi Tom,

Just want to ask is there any difference between doing objects(tables,indexes and etc) reorganization within same tablespace and to new tablepsace? Normally i used to do reorganization to another tablespace but this method will need extra space to move the objects before we able to shrink old tablespace. Recently i used to do reorganozation within same tablespace to reuse the space but seem the reorganization within the same tablespace need to start reorg the objects in end of the blocks so that we able to shrink the size of the tablepsace. There is some incident where i need to reorg big objects few times before it able to move to below blocks and let us to resize. Just wanted whether there is any other differences between reorganization within the same tablespace and to new tablespace.
Thanks.

and we said...

No real difference, but be aware that when you are using the same tablespace then you run the slightly increased risk of *not* being able to reclaim the space you wanted.

eg Lets say your tablespace is currently

F= free space
U = used space
O = object you want to move

and the datafile something like:

Start: FFFFFFFUUUUUUUUUUUBBBBBBBBBBFFFFFFUUUU End

Then a reorg of "B" *might* end with something like:

Start: BBBBBBUUUUUUUUUUUFFFFFFFFFFFFBBBBBUUUUBBBBBBBB End

because during the move we could not get enough freespace *underneath* where B currently resided. As a result, you now have part of B even *higher* in the datafile hwm.

If you have lots of freespace toward the start of the datafile, you'd be unlikely to hit this, but this is why sometimes people go with the new tablespace option. (You can always rename the tablespace later if you need)

Rating

  (1 rating)

Comments

Tharmendran Sukumaran, September 10, 2021 - 4:00 am UTC

Hi Tom,
Thanks for the reply. Appreciate it. Yeah swapping the tablespace name can be done we we should remember to alter the user default user tablespace as well accordingly.
Connor McDonald
September 13, 2021 - 1:57 am UTC

glad we could help

More to Explore

Administration

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