Oracle dbms_space package tips

Phone
800-766-1884

Privacy Policy

Oracle dbms_space package tips
Arup Nanda in OTN

Suppose you have a table BOOKINGS, which holds online bookings from the website. After the booking is confirmed, it's stored in an archival table BOOKINGS_HIST and the row is deleted from BOOKINGS. The time between booking and confirmation varies widely among customers, so a lot of rows are inserted above the HWM of the table because sufficient space is not available from the deleted rows.

Now you need to reclaim wasted space. First, find out exactly how much space is wasted in that segment that can be reclaimed. Because this is in an ASSM-enabled tablespace, you have to use the procedure SPACE_USAGE of the package DBMS_SPACE, as shown below.

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(
      segment_owner      => user,
      segment_name       => 'BOOKINGS',
      segment_type       => 'TABLE',
      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||' Bytes = '||l_fs1_bytes);
   dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
   dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
   dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
   dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
end;
/
The output is:
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 4148 Bytes = 0
Full Blocks = 2 Bytes = 16384

The output shows that there are 4,148 blocks with 75-100% free space (FS4); no other free blocks are available. There are only 2 full blocks. The 4,148 blocks can be recovered.

Next, you must ensure that the table is row-movement enabled. If it's not, you can enable it with:

alter table bookings enable row movement;

or via Enterprise Manager 10g, on the Administration page. You should also ensure that all rowid-based triggers are disabled on this table because the rows are moved and the rowids could change.

Finally, you can reorganize the existing rows of the table with:

alter table bookings shrink space compact;

This command re-distributes the rows inside the blocks as shown in Figure 3, resulting in more free blocks under the HWM—but the HWM itself is not disturbed.

After the operation, let's see the change in space utilization. Using the PL/SQL block shown in the first step, you can see how the blocks are organized now:

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 1 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 2 Bytes = 16384

Note the important change here: the number of FS4 blocks (with 75-100% free space) is now 0, down from 4,148. We also see an increase in FS3 blocks (50-75% free space) from 0 to 1. However, because the HWM has not been reset, the total space utilization remains the same. We can check the space used with:

SQL> select blocks from user_segments where segment_name = 'BOOKINGS';

   BLOCKS
---------
     4224

The number of blocks occupied by the table—4,224—remains the same because the HWM has not moved from its original position. You can move the HWM to a lower position and reclaim the space with

alter table bookings shrink space;

Note that the clause COMPACT is not present. This operation will return the unused blocks to the database and reset the HWM. You can test it by checking the space allocated to the table:

SQL> select blocks from user_segments where segment_name = 'BOOKINGS';

    BLOCKS
----------
         8

The number of blocks is down from 4,224 to 8; all the unused space inside the table was returned to the tablespace for use in other segments. This shrink operation occurs completely online and does not affect users.