what is the use of this stored proc?
it tells you how many blocks have free space for updates, right ?
but it does not tell you how much free space in each block. we can
get free space info. from dba_free_space.
can you show how this proc can be of value to us?
another procs in this package is unused_space.
if it reports 35 blocks. does it mean 35 blocks have never
had data in it ?
it seems that it doesn't report any empty blocks above
the high water mark, does it?
how can we make use of this info ? can you give some examples
that we can use these procedures to help manage space.
thanks, tom.
here is an example showing how to use dbms_space and how to interpret the output. basically between the 2 procedures free blocks and unused space, we'll be able to get: free blocks...... number of blocks on the freelist total blocks..... total blocks allocated to the table total bytes...... total bytes allocated to the table unused blocks.... blocks that have never contained data unused bytes..... the above in bytes it does not tell you how many blocks have free space for updates. we can tell you how many blocks are candidates for inserts (they are on the freelist) and blocks on the freelist have space for updates -- but -- there are blocks in the table that have space for updates but that are not on the freelist. we cannot see them in any report. it does not tell you how much space is free in each block (nothing does, typically there are thousands or hundreds of thousands of blocks in a table -- an analysis of the free space block by block is not practical. we can get an average free space but not block by block). this report does show blocks above the high water mark. unused blocks are exactly the block above the high water mark. you can get most of the information supplied by this package by analyzing the table and using queries against user_tables and user_segments. the freelist analysis is more detailed using this package as you can look at each freelist independently. below is a procedure you can use to make using dbms_space a little easier. after that i create a table and show how space is being used in it after various operations. comments in bold explain the output. ops$tkyte@8i> create or replace 2 procedure show_space 3 ( p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default 'table' ) 6 as 7 l_free_blks number; 8 9 l_total_blocks number; 10 l_total_bytes number; 11 l_unused_blocks number; 12 l_unused_bytes number; 13 l_lastusedextfileid number; 14 l_lastusedextblockid number; 15 l_last_used_block number; 16 procedure p( p_label in varchar2, p_num in number ) 17 is 18 begin 19 dbms_output.put_line( rpad(p_label,40,'.') || 20 p_num ); 21 end; 22 begin 23 dbms_space.free_blocks 24 ( segment_owner => p_owner, 25 segment_name => p_segname, 26 segment_type => p_type, 27 freelist_group_id => 0, 28 free_blks => l_free_blks ); 29 30 dbms_space.unused_space 31 ( segment_owner => p_owner, 32 segment_name => p_segname, 33 segment_type => p_type, 34 total_blocks => l_total_blocks, 35 total_bytes => l_total_bytes, 36 unused_blocks => l_unused_blocks, 37 unused_bytes => l_unused_bytes, 38 last_used_extent_file_id => l_lastusedextfileid, 39 last_used_extent_block_id => l_lastusedextblockid, 40 last_used_block => l_last_used_block ); 41 42 p( 'free blocks', l_free_blks ); 43 p( 'total blocks', l_total_blocks ); 44 p( 'total bytes', l_total_bytes ); 45 p( 'unused blocks', l_unused_blocks ); 46 p( 'unused bytes', l_unused_bytes ); 47 p( 'last used ext fileid', l_lastusedextfileid ); 48 p( 'last used ext blockid', l_lastusedextblockid ); 49 p( 'last used block', l_last_used_block ); 50 end; 51 / procedure created. ops$tkyte@8i> ops$tkyte@8i> create table t ( x int, y char(2000) default '*' ) 2 storage ( initial 40k next 40k minextents 5 ) 3 tablespace system; table created. i create a table with >1 extent to make it interesting. i also put a char(2000) in there to make the minimum row length be 2000 bytes (chars always take their max space right away). this just ma
