When one of our developers was thinking of executing a couple of alter table statements they asked the following questions:
(alter table enable row movement;
alter table shrink space cascade;)
What do I need to consider before executing? Here are
my concerns:
1 Does this command require additional tablespace
to shrink the table?
2 Do I need to make sure nothing is hitting the
table while running the commands?
3 Is there a better way to accomplish the same
thing?
4 Can I expect only a 10% savings in TS as my test
on test I performed?
5 What is the max size of table (or other
limitation) I should consider before executing the command?
Here is the response from one of our great DBA's:
1 No, it doesn't, as it uses same table to move blocks
down below the high water mark (HWM).
2 It depends. Whole operation could be broken down into
two parts:
a. Blocks move-down operation (ALTER TABLE table SHRINK
SPACE COMPACT) causes row locks, leaving whole table unlocked.
b. Amend HWM: ALTER TABLE SHRINK SPACE
(CASCADE) will require exclusive lock on the table for very short time.
3 .Yes. Please try (as long as you have space available),
CREATE TABLE table2 AS SELECT * FROM table1. Then create relevant indexes. Drop
original tables. This will insert all the blocks into new table sequentially,
increasing HWM together with loaded data.
4 No, there is no rule. DBAs have a Segment Advisor tool
that might give approximate saving on particular table.
5. No size limitation, as whole process is done on the
original table, plus some temp space is needed. It also utilizes PGA (Program
Global Area) – memory assigned to a session. Plus, the
I/O utilization . Also for partitioned tables it would be wiser to shrink
partitions instead of entire table (ALTER TABLE table MODIFY PARTITION
partition SHRINK SPACE).
