Oracle PL/SQL Tutorial/System Packages/dbms space

Show table space

SQL>
SQL> create or replace procedure show_space( p_segname in varchar2,
  2    p_owner   in varchar2 default user,
  3    p_type    in varchar2 default "TABLE",
  4    p_partition in varchar2 default NULL )
  5  as
  6      l_free_blks                 number;
  7
  8      l_total_blocks              number;
  9      l_total_bytes               number;
 10      l_unused_blocks             number;
 11      l_unused_bytes              number;
 12      l_LastUsedExtFileId         number;
 13      l_LastUsedExtBlockId        number;
 14      l_last_used_block           number;
 15  begin
 16      dbms_space.free_blocks( segment_owner     => p_owner,
 17        segment_name      => p_segname,
 18        segment_type      => p_type,
 19        partition_name    => p_partition,
 20        freelist_group_id => 0,
 21        free_blks         => l_free_blks );
 22
 23      dbms_space.unused_space( segment_owner     => p_owner,
 24        segment_name      => p_segname,
 25        segment_type      => p_type,
 26        partition_name    => p_partition,
 27        total_blocks      => l_total_blocks,
 28        total_bytes       => l_total_bytes,
 29        unused_blocks     => l_unused_blocks,
 30        unused_bytes      => l_unused_bytes,
 31        last_used_extent_file_id => l_LastUsedExtFileId,
 32        last_used_extent_block_id => l_LastUsedExtBlockId,
 33        last_used_block => l_last_used_block );
 34
 35      dbms_output.put_line( "Free Blocks");
 36      dbms_output.put_line( l_free_blks );
 37      dbms_output.put_line( "Total Blocks");
 38      dbms_output.put_line( l_total_blocks );
 39      dbms_output.put_line( "Total Bytes");
 40      dbms_output.put_line( l_total_bytes );
 41      dbms_output.put_line( "Unused Blocks");
 42      dbms_output.put_line( l_unused_blocks );
 43      dbms_output.put_line( "Unused Bytes");
 44      dbms_output.put_line( l_unused_bytes );
 45      dbms_output.put_line( "Last Used Ext FileId");
 46      dbms_output.put_line( l_LastUsedExtFileId );
 47      dbms_output.put_line( "Last Used Ext BlockId");
 48      dbms_output.put_line( l_LastUsedExtBlockId );
 49      dbms_output.put_line( "Last Used Block");
 50      dbms_output.put_line( l_last_used_block );
 51  end;
 52  /
Procedure created.
SQL>

Use dbms_space.free_blocks and dbms_space.unused_space

SQL> set echo on
SQL>
SQL> create table t ( x int, y char(1000) default "x" );
Table created.
SQL>
SQL> create or replace procedure measure_usage
  2  as
  3      l_free_blks                 number;
  4      l_total_blocks              number;
  5      l_total_bytes               number;
  6      l_unused_blocks             number;
  7      l_unused_bytes              number;
  8      l_LastUsedExtFileId         number;
  9      l_LastUsedExtBlockId        number;
 10      l_LAST_USED_BLOCK           number;
 11
 12      procedure get_data
 13      is
 14      begin
 15          dbms_space.free_blocks
 16          ( segment_owner     =>  USER,
 17            segment_name      => "T",
 18            segment_type      => "TABLE",
 19            FREELIST_group_id => 0,
 20            free_blks         => l_free_blks );
 21
 22          dbms_space.unused_space
 23          ( segment_owner     => USER,
 24            segment_name      => "T",
 25            segment_type      => "TABLE",
 26            total_blocks      => l_total_blocks,
 27            total_bytes       => l_total_bytes,
 28            unused_blocks     => l_unused_blocks,
 29            unused_bytes      => l_unused_bytes,
 30            LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 31            LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 32            LAST_USED_BLOCK => l_last_used_block ) ;
 33
 34
 35          dbms_output.put_line( L_free_blks || " on FREELIST, " ||
 36                                to_number(l_total_blocks-l_unused_blocks-1 ) ||
 37                                " used by table" );
 38      end;
 39  begin
 40      for i in 0 .. 10
 41      loop
 42          dbms_output.put( "insert " || to_char(i,"00") || " " );
 43          get_data;
 44          insert into t (x) values ( i );
 45          commit ;
 46      end loop;
 47
 48
 49      for i in 0 .. 10
 50      loop
 51          dbms_output.put( "update " || to_char(i,"00") || " " );
 52          get_data;
 53          update t set y = null where x = i;
 54          commit;
 55      end loop;
 56  end;
 57  /
Procedure created.
SQL>
SQL> exec measure_usage
insert  00 0 on FREELIST, 0 used by table
insert  01 1 on FREELIST, 1 used by table
insert  02 1 on FREELIST, 1 used by table
insert  03 1 on FREELIST, 1 used by table
insert  04 1 on FREELIST, 1 used by table
insert  05 1 on FREELIST, 1 used by table
insert  06 1 on FREELIST, 1 used by table
insert  07 1 on FREELIST, 1 used by table
insert  08 1 on FREELIST, 2 used by table
insert  09 1 on FREELIST, 2 used by table
insert  10 1 on FREELIST, 2 used by table
update  00 1 on FREELIST, 2 used by table
update  01 1 on FREELIST, 2 used by table
update  02 1 on FREELIST, 2 used by table
update  03 1 on FREELIST, 2 used by table
update  04 2 on FREELIST, 2 used by table
update  05 2 on FREELIST, 2 used by table
update  06 2 on FREELIST, 2 used by table
update  07 2 on FREELIST, 2 used by table
update  08 2 on FREELIST, 2 used by table
update  09 2 on FREELIST, 2 used by table
update  10 2 on FREELIST, 2 used by table
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.