Estimate savings of disk space by shrinking a table

This post shows how to estimate disk space savings of shrinking a table. Here we are using Segment space advisor task.

-- Create a table and make it fragmented

SQL> create table tab1 (a number, b varchar2(10));

Table created.

-- Filling table with 1 million records

begin

  for i in 1 .. 1000000

  loop

     insert into tab1 values(i, 'AAAAAAAAAA');

  end loop;

  commit;

end;

/

SQL> select count(*) from tab1;

  COUNT(*)

----------

   1000000

-- Check the space usage in MB

SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)

-----------------

               23

-- Let us fragment it by deleting 90% of records evenly across table

SQL> delete from tab1 where mod(a, 10) != 1;

900000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tab1;

  COUNT(*)

----------

    100000

-- We see that it still using same space

SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)

-----------------

               23

Here is the script to estimate space savings if we do shrink space.

declare

 tname varchar2(100);

 oid number;

 tid number;

begin

tname:='CHECK_SHRINKABLE_1';

dbms_advisor.create_task (

  advisor_name     => 'Segment Advisor',

  task_id          => tid,

  task_name        => tname);

dbms_advisor.create_object (

  task_name        => tname,

  object_type      => 'TABLE',

  attr1            => 'MURTY',

  attr2            => 'TAB1',

  attr3            => NULL,

  attr4            => NULL,

  attr5            => NULL,

  object_id        => oid);

  dbms_advisor.set_task_parameter(

  task_name        => tname,

  parameter        => 'recommend_all',

  value            => 'TRUE');

dbms_advisor.execute_task(tname);

end;

/

We can use the view USER_ADVISOR_RECOMMENDATIONS view to check result.

SQL> set linesize 110

SQL> select BENEFIT_TYPE from USER_ADVISOR_RECOMMENDATIONS where TASK_NAME='CHECK_SHRINKABLE_1';

BENEFIT_TYPE

--------------------------------------------------------------------------------------------------------------------------------------------

Enable row movement of the table MURTY.TAB1 and perform shrink, estimated savings is 17512778 bytes.

Let us shrink the table and check the space usage.

SQL> alter table tab1 enable row movement;

Table altered.

SQL> alter table tab1 shrink space;

Table altered.

SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)

-----------------

           2.4375