|Reclaiming Unused Space in Datafiles - This article shows several methods for reclaiming unused space from datafiles.|
Martin said...A nice feature (I think starting with 10g) is that empty datafiles can be dropped from tablespaces now as well (e.g. if accidentially a wrong file was added).
John said...Thanks for puting this together. Very useful, but in my testing I found lots of issues. I think you should point out:
John said...oops. hit enter before adding the issues.
1. reorganize in oem requires host based access from oem to the target db host.
2. if the tablespace contains any objects which contain timestamp or lob you cannot use the reorganize option.
3. you did not mention coalesce (which may be because it's so useless).
4. if oem can generate a script to do the reorganization online, it would be nice to see.
Diego said...Hi there, thanks for the wrap up. I like how you show the manual steps before jumping into graphical tools.
If you go for an export/import approach, I have found that you can use "transform=segment_attributes:n" in the impdp command prevent Oracle from remembering how much space (extents) it should give to each table.
Yes. Nice point. I've added a reference to that at the end of that section. It doesn't affect this example as I used a truncate, but if I had performed lots of deletes on the tables, it could well have resulted in more space savings.
eMarcel said...SuperB! Clear and useful article. Thanks!
Wouter said...Great post
I reorganized a tblsp using EM and a scratch tblsp . This crashed because of a ctxsys index that could not be moved. The tables that were already moved where left in the SCRATCH tblsp.
Solved this by dropping the ctxsys indexes, restarting the reorg. Then used EM to do a reorg of the SCRATCH tblsp and copied the first half of the proposed SQL to move the objects back