Oracle PL/SQL Tutorial/Table/Table Storage

Measure table storage

   <source lang="sql">

SQL> set echo on SQL> SQL> 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
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  /

SP2-0804: Procedure created with compilation warnings 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>