Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

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.

 

 

 

 

   

 Copyright © 1996 -2017 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks