dbms_space.free_space[6]

l_sql := l_sql || l_conj || p_name || ' like ''' || upper(p_value) || ''''; 36 l_conj := ' and '; 37 elsif ( p_value is not null ) 38 then 39 l_sql := l_sql || l_conj || p_name || ' = ''' || upper(p_value) || ''''; 40 l_conj := ' and '; 41 end if; 42 end; 43 begin 44 l_sql := 'select owner, segment_name, segment_type, partition_name 45 from dba_segments '; 46 47 add_predicate( 'segment_name', p_segname ); 48 add_predicate( 'owner', p_owner ); 49 add_predicate( 'segment_type', p_type ); 50 add_predicate( 'partition', p_partition ); 51 52 execute immediate 'alter session set cursor_sharing=force'; 53 open l_cursor for l_sql; 54 execute immediate 'alter session set cursor_sharing=exact'; 55 56 loop 57 fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name; 58 dbms_output.put_line( l_segment_name || ',' || l_segment_type ); 59 exit when l_cursor%notfound; 60 begin 61 dbms_space.free_blocks 62 ( segment_owner => l_owner, 63 segment_name => l_segment_name, 64 segment_type => l_segment_type, 65 partition_name => l_partition_name, 66 freelist_group_id => 0, 67 free_blks => l_free_blks ); 68 69 dbms_space.unused_space 70 ( segment_owner => l_owner, 71 segment_name => l_segment_name, 72 segment_type => l_segment_type, 73 partition_name => l_partition_name, 74 total_blocks => l_total_blocks, 75 total_bytes => l_total_bytes, 76 unused_blocks => l_unused_blocks, 77 unused_bytes => l_unused_bytes, 78 last_used_extent_file_id => l_lastusedextfileid, 79 last_used_extent_block_id => l_lastusedextblockid, 80 last_used_block => l_last_used_block ); 81 82 pipe row ( show_space_type( l_owner, l_segment_name, l_partition_name, 83 l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks, 84 l_lastusedextfileid, l_lastusedextblockid, l_last_used_block ) ); 85 exception 86 when others then null; 87 end; 88 end loop; 89 close l_cursor; 90 91 return; 92 end; 93 / function created. ops$tkyte@ora9i.world> set arraysize 1 ops$tkyte@ora9i.world> select segment_name, segment_type, free_blocks,total_blocks,unused_blocks 2 from table( show_space_for( '%',user,'%' ) ) 3 / segment_name segment_type free_blocks total_blocks unused_blocks --------------- ----------------- ----------- ------------ ------------- keep_scn table 1 64 62 employees table 0 64 63 stinky table 0 64 63 object_table table 1 64 62 run_stats table 2 64 53 emp table 0 64 62 proj table 0 64 62 x table 1 64 62 words table 0 64 63 docs table 0 64 63 keywords table 0 64 63 dept table 2 64 61 c table 1 64 62 dsinvlines table 1 64 62 num_str table 1 64 23 t table 4 64 28 t1 table 0 64 63 t2 table 0 64 63 bom table 1 64 62 parts table 1 64 62 sys_c001371 index 0 64 62 sys_c001372 index 0 64 62 sys_c001574 index 0 64 62 sys_c001694 index 0 64 62 sys_c001695 index 0 64 62 bom_pk index 0 64 62 parts_pk