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>