back to the basics: ALTER TABLE MOVE vs SHRINK

This time let’s examine the difference between move and shrink – this is very common question on a lot of trainings. You can find a lot of great articles in the Internet regarding this subject but I think that visualisation really helps to understand what is really going on at the low level.

As you can see most of the table blocks have less then 50% of rows present in the block. You should understand that DELETE does not remove anything from the table block – the rows are only marked as deleted by flag 3C (the regular row is marked by flag 2C).

So this means that more then a half rows were marked as deleted in almost every block in a table.
Now let’s check what will happen after ALTER TALBE MOVE.

So what has happened in here? MOVE command forced building of a brand new segment. You can see, that DATA_OBJECT_ID has changed – that’s why I had to update the config file:

[oracle@rico ~]$ cat odbv_show
94710:X|RED
94711:X|BLUE

Now the blue "X" represents the new instance of the table (the new segment) and the red "X" represents the old one. As you can see, the blocks were not removed from a tablespace. To be honest – they were just marked as reusable.

So let’s check what will happen when I’ll create a new table in the same tablespace:

As you can see, most of the blocks were consumed by blue "Y" (representation of table EMPLOYEES_SHRINK2).

Below you can see what will happen after executing ALTER TABLE SHRINK SPACE COMPACT and creating a new table in the same tablespace:

As you can see, that a new table didn’t consume blocks of the old one. Old blocks are being reserved for the first table and will reused by it, when new data arrives.

So to sum things up:

ALTER TABLE MOVE creates a new physical segment at the end of datafile and assigns new DATA_OBJECT_ID (this is one of the reasons, you have to rebuild indexes after that)

ALTER TABLE SHRINK SPACE doesn’t create a new segment and does not change DATA_OBJECT_ID – instead the rows are being moved inside a blocks to defragment the object. Old extents can be reused by the same object or by other objects in the same tablespace

ALTER TABLE SHRINK SPACE COMPACT doesn’t create a new segment and does not change DATA_OBJECT_ID – instead the rows are being moved inside a blocks to defragment the object. Old extents can be reused only by the same object