ok, time for an update of this utility! i had this sitting around already -- it
does two things
1) adds partition support
2) makes it so this runs in sql for anything... gives a result set instead of
printing. you can easily make it dbms_output.put_line if you want...
first we start with the types:
create or replace type show_space_type
as object
( owner varchar2(30),
segment_name varchar2(30),
partition_name varchar2(30),
segment_type varchar2(30),
free_blocks number,
total_blocks number,
unused_blocks number,
last_used_ext_fileid number,
last_used_ext_blockid number,
last_used_block number
)
/
create or replace type show_space_table_type
as table of show_space_type
/
and then the function:
create or replace
function show_space_for
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'table',
p_partition in varchar2 default null )
return show_space_table_type
authid current_user
as
pragma autonomous_transaction;
type rc is ref cursor;
l_cursor rc;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_lastusedextfileid number;
l_lastusedextblockid number;
l_last_used_block number;
l_sql long;
l_conj varchar2(7) default ' where ';
l_data show_space_table_type :=
show_space_table_type();
l_owner varchar2(30);
l_segment_name varchar2(30);
l_segment_type varchar2(30);
l_partition_name varchar2(30);
procedure add_predicate( p_name in varchar2, p_value in varchar2 )
as
begin
if ( instr( p_value, '%' ) > 0 )
then
l_sql := l_sql || l_conj || p_name ||
' like ''' || upper(p_value) || '''';
l_conj := ' and ';
elsif ( p_value is not null )
then
l_sql := l_sql || l_conj || p_name ||
' = ''' || upper(p_value) || '''';
l_conj := ' and ';
end if;
end;
begin
l_sql := 'select owner, segment_name, segment_type, partition_name
from dba_segments ';
add_predicate( 'segment_name', p_segname );
add_predicate( 'owner', p_owner );
add_predicate( 'segment_type', p_type );
add_predicate( 'partition', p_partition );
execute immediate 'alter session set cursor_sharing=force';
open l_cursor for l_sql;
execute immediate 'alter session set cursor_sharing=exact';
loop
fetch l_cursor into l_owner, l_segment_name, l_segment_type,
l_partition_name;
exit when l_cursor%notfound;
begin
dbms_space.free_blocks
( segment_owner => l_owner,
segment_name => l_segment_name,
segment_type => l_segment_type,
partition_name => l_partition_name,
freelist_group_id => 0,
free_blks => l_free_blks );
dbms_space.unused_space
( segment_owner => l_owner,
segment_name => l_segment_name,
segment_type => l_segment_type,
partition_name => l_partition_name,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_lastusedextfileid,
last_used_extent_block_id => l_lastusedextblockid,
last_used_block => l_last_used_block );
l_data.extend;
l_data(l_data.count) :=
show_space_type( l_owner, l_segment_name, l_partition_name,
l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks,
l_lastusedextfileid, l_lastusedextblockid, l_last_used_block
);
exception
when others then null;
end;
end loop;
close l_cursor;
return l_data;
end;
/
then we can:
ops$tkyte@ora817dev.us.oracle.com> select segment_name, partition_name
segment_type, free_blocks,total_blocks,unused_blocks
2 from table( cast( show_space_for( 'hashed',user,'%' ) as
show_space_table_type ) )
3 /
segment_na segment_type free_blocks total_blocks unused_blocks
---------- ----------------- ----------- ------------ -------------
hashed part_2 1 64 62
hashed part_3 1 64 62
hashed part_4 1 64 62
hashed part_1 1 64 62
ops$tkyte@ora817dev.us.oracle.com>
and in 9i, we'd change the function to be pipelined:
ops$tkyte@ora9i.world> create or replace
2 function show_space_for
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'table',
6 p_partition in varchar2 default null )
7 return show_space_table_type
8 authid current_user
9 pipelined
10 as
11 pragma autonomous_transaction;
12 type rc is ref cursor;
13 l_cursor rc;
14
15 l_free_blks number;
16 l_total_blocks number;
17 l_total_bytes number;
18 l_unused_blocks number;
19 l_unused_bytes number;
20 l_lastusedextfileid number;
21 l_lastusedextblockid number;
22 l_last_used_block number;
23 l_sql long;
24 l_conj varchar2(7) default ' where ';
25 l_owner varchar2(30);
26 l_segment_name varchar2(30);
27 l_segment_type varchar2(30);
28 l_partition_name varchar2(30);
29
30 procedure add_predicate( p_name in varchar2, p_value in varchar2 )
31 as
32 begin
33 if ( instr( p_value, '%' ) > 0 )
34 then
35
本文关键：dbms_space.free_space
本站最佳浏览方式为 分辨率 1024x768 IE 6.0（或更高版本的 IE浏览器） 
|
5 extents 40k each (8k block size) why not 5x5=25 blocks? october 22, 2001 
free space october 30, 2001