Hi there.
I'm experiencing a problem I did not use to have. First of all, a description of our envorinnement:
We have some big partitioned tables and for performance optimization our ETLs use bluk, append hints, parallelism and so on. This create several unused space holes in the tablespaces/datafiles thus a kind of space leak on our disks.
A full correction would be recreate the tablespaces moving all it's objects to a new one. this would be impratical since there are about 15 which are above 100GB; the time and effort to recreate everything is not affordable for the businnes.
Instead we have a single proc that just calculate the amount of real used space (converted to blocks) and performs a move of every object above this block_id. Right after this operation it does a dynamic shrink based on the new HWM (since the objects have been moved) on the datafile thus freeing up disk space. As we have one datafile per tablespace and one tablespace per schema we would like to keep this organization, so we perform a single move for the objects, like 'ALTER TABLE '||owner||'.'||segment_name||' MOVE;' (the full query works with all type of data objects like table partitions, index partitions and subpartions). This will move the object into the same tablespace to the firsts freespace on the tables and free up space in the end of the file allowing the shrink. In theory.
This single proc used to work fine. In a tablespace of 650GB with 530GB in use moving about 20GB (the amount of data above the 530GB HWM) is simpler than creating a new file/tbs and moving 20GB is faster than 530GB.
But things suddenly changed when some tbs refused to be shrunk. What I found out: the move command does not fail, it runs fine and Oracle really moves the object. But for reasons that I ignore it doesn't move it to the begining of the file, it keeps the object at the end. So the proc calculates the new HWM but because some objects that were kept in the tail of the file, the shrink is performed with a very high HWM, so no real space is reclaimed.
So, the main question: How does the ALTER TABLE FOO MOVE really works? I thought it would always move the object to the begining of the file thus reorganizing it, but I analyzed the last objects that gave me this problem (block_id before and after the move, compared with empty block_ids and all) and in fact I see that they were moved to the end of the file, although there was enough space to accomodate them in the begining.