Reorg Tablespace - gain space?

[Former Member]

If I reorg my tablespaces will I gain space on my OS level?

Thanks,

-- Jackie

[stefan_koehler · Accepted solution]

Hello Jackie,

if you move (reorg) the database objects to another tablespace and delete the old "empty" tablespace, then you will gain space on OS level.

If you just reorg the objects in the same tablespace, you will maybe need more space on OS level (temporary space for the reorg objects will extend the data files), but you will have more free space in the tablespace itself after the reorg.

Regards

Stefan

[Former Member · Accepted solution]

If you have done a massive delete - and reorg to a different tablespace - you can shrink a tablespace up to the "highest" used block in a tablespace - I hope that is clear.

If you are just rebuilding indexes and reorging data to gain space, it is highly likely the tablespace will grow right back to the size it was before the reorg quickly.

This has some good info"

http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

Check the "What's at the End of the File?" section.