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
