Shrink space on index

Script to check current space usage of a table or an index: shrink_space_candidate_check_usage.sql


cat shrink_space_candidate_check_usage.sql

set serverout on verify off echo off
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage('&owner',
'&name',
'&type',
fs1_bytes=> l_fs1_bytes,
fs1_blocks         => l_fs1_blocks,
fs2_bytes          => l_fs2_bytes,
fs2_blocks         => l_fs2_blocks,
fs3_bytes          => l_fs3_bytes,
fs3_blocks         => l_fs3_blocks,
fs4_bytes          => l_fs4_bytes,
fs4_blocks         => l_fs4_blocks,
full_bytes         => l_full_bytes,
full_blocks        => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes  => l_unformatted_bytes   );
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' and Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' and Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' and Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' and Bytes = '||l_fs4_bytes);
dbms_output.put_line(' Full Blocks = '||l_full_blocks||' and Bytes = '||l_full_bytes);
dbms_output.put_line(' Unformatted Blocks = '||l_unformatted_blocks||' and Bytes ='||l_unformatted_bytes);
end;
/
quit;
EOF

Before calling ‘SHRINK SPACE’, check current spage usage.


SQL> @shrink_space_candidate_check_usage.sql

Enter value for owner: SP84P
Enter value for name: DBXTESPTP1
Enter value for type: INDEX

FS1 Blocks = 0 and Bytes = 0
FS2 Blocks = 218 and Bytes = 1785856
FS3 Blocks = 0 and Bytes = 0
FS4 Blocks = 0 and Bytes = 0
Full Blocks = 445624 and Bytes = 3650551808
Unformatted Blocks = 1360 and Bytes =11141120

As show above it has 1785856 bytes in low usage blocks (FS2 refers to 25-50% occupation) and 11141120 bytes which has never been used. Now call ‘shrink space compact’ on index.


SQL> alter index SP84P.DBXTESPTP1 shrink space compact;
Index altered.

the effect:

SQL> @shrink_space_candidate_check_usage.sql
 Enter value for owner: SP84P
 Enter value for name: DBXTESPTP1
 Enter value for type: INDEX
 FS1 Blocks = 0 and Bytes = 0
 FS2 Blocks = 3 and Bytes = 24576
 FS3 Blocks = 0 and Bytes = 0
 FS4 Blocks = 110310 and Bytes = 903659520
 Full Blocks = 335529 and Bytes = 2748653568
 Unformatted Blocks = 1360 and Bytes =11141120

PL/SQL procedure successfully completed.

As we can see, SHRINK SPACE COMPACT move blocks from FS2 to FS4. Now call ‘shrink space’ on index.


SQL> alter index SP84P.DBXTESPTP1 shrink space;
Index altered.

the effect:


SQL> @shrink_space_candidate_check_usage.sql
Enter value for owner: SP84P
Enter value for name: DBXTESPTP1
Enter value for type: INDEX
FS1 Blocks = 0 and Bytes = 0
FS2 Blocks = 0 and Bytes = 0
FS3 Blocks = 0 and Bytes = 0
FS4 Blocks = 0 and Bytes = 0
Full Blocks = 335517 and Bytes = 2748555264
Unformatted Blocks = 0 and Bytes =0

PL/SQL procedure successfully completed.

As shown above, the unformatted blocks are move off from HWM and total allocated space is shrinked from 3.6GB to 2.7GB. We saved 900MB to tablespace.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: