Another Way to Reorgnize Table in Oracle

After inserting/deleting from a table for a long time, the table may contain much spared space that hurts the full table scan greatly. Instead of export and import, or recreate it. Move it from one tablespace to another is another way.

Here's a case I did recently. I noticed a table is slow when running full table scan, however it contains only 30K rows, and each row is not that big.

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME        MB_BYTES   BLOCKS     EXTENTS
------------------- ---------- ---------- ----------
BAD_TABLE           220        28160      99

OK, move it to another tablespace:

alter table BAD_TABLE move tablespace tb_another;

Run that check again:

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME           MB_BYTES   BLOCKS     EXTENTS
---------------------- ---------- ---------- ----------
BAD_TABLE              32         4096       47

It's much smaller. Well, you may want to move it back to its original tablespace.