Alter table shrink
command
Arup Nanda in OTNYou can also compact the indexes of the
table in one statement:
alter table bookings shrink space cascade;
The online shrink command is a powerful feature for
reclaiming wasted space and resetting the HWM. I consider the
latter—resetting of the HWM—the most useful result of this
command because it improves the performance of full table scans.
Finding Candidates for Shrinking
Before performing an online shrink, you may want to find out
the biggest bang-for-the-buck by identifying the segments that
can be most fully compressed. Simply use the built-in function
verify_shrink_candidate in the package dbms_space.
Execute this PL/SQL code to test if the segment can be shrunk to
1,300,000 bytes:
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',1300000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/
PL/SQL procedure successfully completed.
SQL> print x
X
--------------------------------
T
If you use a low number for the target shrinkage, say 3,000:
begin
if (dbms_space.verify_shrink_candidate
('ARUP','BOOKINGS','TABLE',30000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
the value of the variable x is set to 'F', meaning the table
cannot be shrunk to 3,000 bytes.
