点击(此处)折叠或打开
- 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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2116162/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28539951/viewspace-2116162/