131 lines
6.5 KiB
SQL
131 lines
6.5 KiB
SQL
set serveroutput on
|
|
set verify off
|
|
|
|
DECLARE
|
|
|
|
l_segment_owner varchar2(30);
|
|
l_segment_name varchar2(30);
|
|
l_partition_name varchar2(30);
|
|
l_segment_type varchar2(30);
|
|
l_unformatted_blocks number;
|
|
l_unformatted_bytes number;
|
|
l_fs1_blocks number; -- Number of blocks that has at least 0 to 25% free space
|
|
l_fs1_bytes number; -- Number of bytes that has at least 0 to 25% free space
|
|
l_fs2_blocks number; -- Number of blocks that has at least 25 to 50% free space
|
|
l_fs2_bytes number; -- Number of bytes that has at least 25 to 50% free space
|
|
l_fs3_blocks number; -- Number of blocks that has at least 50 to 75% free space
|
|
l_fs3_bytes number; -- Number of bytes that has at least 50 to 75% free space
|
|
l_fs4_blocks number; -- Number of blocks that has at least 75 to 100% free space
|
|
l_fs4_bytes number; -- Number of bytes that has at least 75 to 100% free space
|
|
l_full_blocks number; -- Total number of blocks that are full in the segment
|
|
l_full_bytes number; -- Total number of bytes that are full in the segment
|
|
l_total_blocks number;
|
|
l_total_bytes number;
|
|
l_unused_blocks number;
|
|
l_unused_bytes number;
|
|
l_last_used_extent_file_id number;
|
|
l_last_used_extent_block_id number;
|
|
l_last_used_block number;
|
|
l_free_blks number;
|
|
l_segment_space_management varchar2(30);
|
|
|
|
BEGIN
|
|
|
|
select ts.segment_space_management
|
|
into l_segment_space_management
|
|
from dba_segments seg, dba_tablespaces ts
|
|
where seg.owner = '&&segment_owner'
|
|
and seg.segment_name = '&&segment_name'
|
|
and ( '&&partition_name' is null
|
|
or seg.partition_name = '&&partition_name'
|
|
)
|
|
and seg.tablespace_name = ts.tablespace_name;
|
|
|
|
if l_segment_space_management = 'AUTO'
|
|
then
|
|
|
|
dbms_space.space_usage
|
|
( segment_owner => '&&segment_owner',
|
|
segment_name => '&&segment_name',
|
|
segment_type => '&&segment_type',
|
|
partition_name => '&&partition_name',
|
|
unformatted_blocks => l_unformatted_blocks,
|
|
unformatted_bytes => l_unformatted_bytes,
|
|
fs1_blocks => l_fs1_blocks,
|
|
fs1_bytes => l_fs1_bytes,
|
|
fs2_blocks => l_fs2_blocks,
|
|
fs2_bytes => l_fs2_bytes,
|
|
fs3_blocks => l_fs3_blocks,
|
|
fs3_bytes => l_fs3_bytes,
|
|
fs4_blocks => l_fs4_blocks,
|
|
fs4_bytes => l_fs4_bytes,
|
|
full_blocks => l_full_blocks,
|
|
full_bytes => l_full_bytes
|
|
);
|
|
|
|
dbms_output.put_line('blocks');
|
|
dbms_output.put_line(' unformatted : ' || to_char(l_unformatted_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 0 to 25% free space : ' || to_char(l_fs1_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 25 to 50% free space : ' || to_char(l_fs2_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 50 to 75% free space : ' || to_char(l_fs3_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 75 to 100% free space : ' || to_char(l_fs4_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' full : ' || to_char(l_full_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.new_line;
|
|
dbms_output.put_line('bytes');
|
|
dbms_output.put_line(' unformatted : ' || to_char(l_unformatted_bytes, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 0 to 25% free space : ' || to_char(l_fs1_bytes, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 25 to 50% free space : ' || to_char(l_fs2_bytes, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 50 to 75% free space : ' || to_char(l_fs3_bytes, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' 75 to 100% free space : ' || to_char(l_fs4_bytes, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' full : ' || to_char(l_full_bytes, '9G999G999G999G999G999'));
|
|
|
|
else
|
|
|
|
dbms_space.free_blocks
|
|
( segment_owner => '&&segment_owner',
|
|
segment_name => '&&segment_name',
|
|
segment_type => '&&segment_type',
|
|
freelist_group_id => 0,
|
|
free_blks => l_free_blks
|
|
);
|
|
|
|
dbms_output.put_line('free blocks : ' || to_char(l_free_blks, '9G999G999G999G999G999'));
|
|
|
|
end if;
|
|
|
|
dbms_space.unused_space
|
|
( segment_owner => '&&segment_owner',
|
|
segment_name => '&&segment_name',
|
|
segment_type => '&&segment_type',
|
|
partition_name => '&&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_last_used_extent_file_id,
|
|
last_used_extent_block_id => l_last_used_extent_block_id,
|
|
last_used_block => l_last_used_block
|
|
);
|
|
|
|
dbms_output.put_line('blocks');
|
|
dbms_output.put_line(' total : ' || to_char(l_total_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' unused : ' || to_char(l_unused_blocks, '9G999G999G999G999G999'));
|
|
dbms_output.new_line;
|
|
dbms_output.put_line('bytes');
|
|
dbms_output.put_line(' total : ' || to_char(l_total_bytes, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' unused : ' || to_char(l_unused_bytes, '9G999G999G999G999G999'));
|
|
dbms_output.new_line;
|
|
dbms_output.put_line('last used extent');
|
|
dbms_output.put_line(' file id : ' || to_char(l_last_used_extent_file_id, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' starting block id : ' || to_char(l_last_used_extent_block_id, '9G999G999G999G999G999'));
|
|
dbms_output.put_line(' last block : ' || to_char(l_last_used_block, '9G999G999G999G999G999'));
|
|
|
|
END;
|
|
/
|
|
|
|
undefine segment_owner
|
|
undefine segment_name
|
|
undefine segment_type
|
|
undefine partition_name
|
|
|