How To See The Last Used Block Or Extent

Today I like show the way of see the last used block in a table (exten too) when we perform, by example, an insert, remember that if it have free blocks at the beginning or in the middle it would use them and not neccesary go to above the hwm (high watermark).

For this proposer we’re going to use the dbms_space.unused_space package.

From Oracle Doc we can see the description for the next fields, this attributes get us the data which we are looking for;

last_used_extent_ file_id -> Returns the file ID of the last extent which contains data.

last_used_extent_ block_id -> Returns the starting block ID of the last extent which contains data.

last_used_block -> Returns the last block within this extent which contains data.

The next script is a simple example of how it works, you need pass the table owner, and table name;

 
set serveroutput on
declare

   TOTAL_BLOCKS              number;
   TOTAL_BYTES               number;
   UNUSED_BLOCKS             number;
   UNUSED_BYTES              number;
   LAST_USED_EXTENT_FILE_ID  number;
   LAST_USED_EXTENT_BLOCK_ID number;
   LAST_USED_BLOCK           number;

 begin
   dbms_space.unused_space('&prop.','&tabname','TABLE',
      TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
      LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
      LAST_USED_BLOCK);

    dbms_output.put_line('LAST USED EXTEN AND BLOCK');
    dbms_output.put_line('-----------------------------------');
    dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||LAST_USED_EXTENT_FILE_ID);
    dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
    dbms_output.put_line('LAST_USED_BLOCK           = '||LAST_USED_BLOCK);

end;
/
set serveroutput off

HTH – Antonio NAVARRO

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