点击(此处)折叠或打开
-
declare
-
--输入段信息
-
p_owner varchar2(255):='SCOTT';
-
p_seg_name varchar2(255):='T_TEST01';
-
p_type varchar2(255):='TABLE';
-
p_partition varchar2(255);
-
-
v_free_blocks number;
-
-
v_total_blocks number;
-
v_total_bytes number;
-
v_unused_blocks number;
-
v_unused_bytes number;
-
v_last_used_ext_file_id number;
-
v_last_used_ext_block_id number;
-
v_last_used_block number;
-
-
v_segment_space_mgmt varchar2(255);
-
-
v_unformatted_blocks number;
-
v_unformatted_bytes number;
-
v_fs1_blocks number;
-
v_fs1_bytes number;
-
v_fs2_blocks number;
-
v_fs2_bytes number;
-
v_fs3_blocks number;
-
v_fs3_bytes number;
-
v_fs4_blocks number;
-
v_fs4_bytes number;
-
v_full_blocks number;
-
v_full_bytes number;
-
begin
-
--判断是段空间管理是auto还是manual
-
select ts.segment_space_management into v_segment_space_mgmt
-
from dba_segments seg,dba_tablespaces ts
-
where seg.tablespace_name=ts.tablespace_name
-
and seg.segment_name=p_seg_name
-
and seg.owner=p_owner
-
and (p_partition is null or seg.partition_name=p_partition);
-
-
if v_segment_space_mgmt='AUTO' then
-
--dbms_space.space_usage
-
dbms_space.space_usage(segment_owner =>p_owner,
-
segment_name =>p_seg_name,
-
segment_type =>p_type,
-
unformatted_blocks =>v_unformatted_blocks,
-
unformatted_bytes =>v_unformatted_bytes,
-
fs1_blocks => v_fs1_blocks,
-
fs1_bytes => v_fs1_bytes,
-
fs2_blocks => v_fs2_blocks,
-
fs2_bytes => v_fs2_bytes,
-
fs3_blocks => v_fs3_blocks,
-
fs3_bytes => v_fs3_bytes,
-
fs4_blocks => v_fs4_blocks,
-
fs4_bytes => v_fs4_bytes,
-
full_blocks =>v_full_blocks,
-
full_bytes =>v_full_bytes,
-
partition_name =>p_partition
-
);
-
dbms_output.put_line('unformatted_blocks :'||v_unformatted_blocks);
-
dbms_output.put_line('fs1_blocks (0-25):'|| v_fs1_blocks);
-
dbms_output.put_line('fs2_blocks (25-50):'|| v_fs2_blocks);
-
dbms_output.put_line('fs3_blocks (50-75):'|| v_fs3_blocks);
-
dbms_output.put_line('fs4_blocks (75-100):'|| v_fs4_blocks);
-
dbms_output.put_line('full_blocks :'||v_full_blocks);
-
else
-
--dbms_space.free_blocks
-
dbms_space.free_blocks(segment_owner =>p_owner,
-
segment_name =>p_seg_name,
-
segment_type =>p_type,
-
freelist_group_id =>0,
-
free_blks =>v_free_blocks,
-
partition_name =>p_partition
-
);
-
dbms_output.put_line('free_blks:'||v_free_blocks);
-
end if;
-
--dbms_space.unused_space
-
dbms_space.unused_space(segment_owner =>p_owner,
-
segment_name =>p_seg_name,
-
segment_type =>p_type,
-
total_blocks =>v_total_blocks,
-
total_bytes =>v_total_bytes,
-
unused_blocks =>v_unused_blocks,
-
unused_bytes =>v_unused_bytes,
-
last_used_extent_file_id =>v_last_used_ext_file_id,
-
last_used_extent_block_id =>v_last_used_ext_block_id,
-
last_used_block =>v_last_used_block,
-
partition_name =>p_partition
-
);
-
dbms_output.put_line('total_blocks :'||v_total_blocks);
-
dbms_output.put_line('total_bytes :'||v_total_bytes);
-
dbms_output.put_line('unused_blocks :'||v_unused_blocks);
-
dbms_output.put_line('unused_bytes :'||v_unused_bytes);
-
dbms_output.put_line('last_used_extent_file_id :'||v_last_used_ext_file_id);
-
dbms_output.put_line('last_used_extent_block_id :'||v_last_used_ext_block_id);
-
dbms_output.put_line('last_used_block :'||v_last_used_block);
- end;