123 lines
9.6 KiB
MySQL
123 lines
9.6 KiB
MySQL
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;
|
|
/
|
|
|
|
|
|
|