Create a stored procedure to measure a table usage : Utility Procedure « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Utility Procedure

Create a stored procedure to measure a table usage

 
SQL>
SQL> set echo on
SQL>
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      procedure get_data is
 12      begin
 13          dbms_space.free_blocks
 14          ( segment_owner     =>  USER,
 15            segment_name      => 'T',
 16            segment_type      => 'TABLE',
 17            FREELIST_group_id => 0,
 18            free_blks         => l_free_blks );
 19
 20          dbms_space.unused_space
 21          ( segment_owner     => USER,
 22            segment_name      => 'T',
 23            segment_type      => 'TABLE',
 24            total_blocks      => l_total_blocks,
 25            total_bytes       => l_total_bytes,
 26            unused_blocks     => l_unused_blocks,
 27            unused_bytes      => l_unused_bytes,
 28            LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 29            LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 30            LAST_USED_BLOCK => l_last_used_block ) ;
 31
 32
 33          dbms_output.put_line( L_free_blks || ' on FREELIST, ' ||
 34                                to_number(l_total_blocks-l_unused_blocks-1 ) ||
 35                                ' used by table' );
 36      end;
 37  begin
 38      for i in 0 .. 10
 39      loop
 40          dbms_output.put( 'insert ' || to_char(i,'00') || ' ' );
 41          get_data;
 42          insert into t (x) values ( i );
 43          commit ;
 44      end loop;
 45
 46
 47      for i in 0 .. 10
 48      loop
 49          dbms_output.put( 'update ' || to_char(i,'00') || ' ' );
 50          get_data;
 51          update t set y = null where x = i;
 52          commit;
 53      end loop;
 54  end;
 55  /

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>
SQL> drop table t;

Table dropped.

SQL>
SQL> --

 

Related examples in the same category