Oracle PL/SQL Tutorial/System Packages/dbms space

Show table space

   <source lang="sql">

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></source>

Use dbms_space.free_blocks and dbms_space.unused_space

   <source lang="sql">

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.</source>