Shrinking segments

Shrinking of a segment makes unused space available to other segments in the tablespace and may improve the performance of queries and DML operations.

The function VERIFY_SHRINK_CANDIDATE in the DBMS_SPACE is an undocumented function which informs us about the size of the segment upto which we can shrink.

declare
res boolean;
begin
res := dbms_space.verify_shrink_candidate('SCOTT','EMP','TABLE',1032192);
if (res)
then dbms_output.put_line('Can shrink to this size');
end if;
end;
/

Can shrink to this size

PL/SQL procedure successfully completed.



declare
res boolean;
begin
res := dbms_space.verify_shrink_candidate('SCOTT','EMP','TABLE',1032191);
if (res)
then dbms_output.put_line('Can shrink to this size');
end if;
end;
/

PL/SQL procedure successfully completed.

In the above example we can shrink a segment to 1032192 bytes.