oracle實用sql(8)--segment show space

selectshen發表於2016-06-06
OracleSQL

點選(此處)摺疊或開啟

  1. declare
  2.   --輸入段資訊
  3.   p_owner varchar2(255):='SCOTT';
  4.   p_seg_name varchar2(255):='T_TEST01';
  5.   p_type varchar2(255):='TABLE';
  6.   p_partition varchar2(255);
  7.   
  8.   v_free_blocks number;
  9.   
  10.   v_total_blocks number;
  11.   v_total_bytes number;
  12.   v_unused_blocks number;
  13.   v_unused_bytes number;
  14.   v_last_used_ext_file_id number;
  15.   v_last_used_ext_block_id number;
  16.   v_last_used_block number;
  17.   
  18.   v_segment_space_mgmt varchar2(255);
  19.   
  20.   v_unformatted_blocks number;
  21.   v_unformatted_bytes number;
  22.   v_fs1_blocks number;
  23.   v_fs1_bytes number;
  24.   v_fs2_blocks number;
  25.   v_fs2_bytes number;
  26.   v_fs3_blocks number;
  27.   v_fs3_bytes number;
  28.   v_fs4_blocks number;
  29.   v_fs4_bytes number;
  30.   v_full_blocks number;
  31.   v_full_bytes number;
  32. begin
  33.    --判斷是段空間管理是auto還是manual
  34.    select ts.segment_space_management into v_segment_space_mgmt
  35.    from dba_segments seg,dba_tablespaces ts
  36.    where seg.tablespace_name=ts.tablespace_name
  37.    and seg.segment_name=p_seg_name
  38.    and seg.owner=p_owner
  39.    and (p_partition is null or seg.partition_name=p_partition);
  40.    
  41.    if v_segment_space_mgmt='AUTO' then
  42.      --dbms_space.space_usage
  43.      dbms_space.space_usage(segment_owner =>p_owner,
  44.                          segment_name =>p_seg_name,
  45.                          segment_type =>p_type,
  46.                          unformatted_blocks =>v_unformatted_blocks,
  47.                          unformatted_bytes =>v_unformatted_bytes,
  48.                          fs1_blocks => v_fs1_blocks,
  49.                          fs1_bytes => v_fs1_bytes,
  50.                          fs2_blocks => v_fs2_blocks,
  51.                          fs2_bytes => v_fs2_bytes,
  52.                          fs3_blocks => v_fs3_blocks,
  53.                          fs3_bytes => v_fs3_bytes,
  54.                          fs4_blocks => v_fs4_blocks,
  55.                          fs4_bytes => v_fs4_bytes,
  56.                          full_blocks =>v_full_blocks,
  57.                          full_bytes =>v_full_bytes,
  58.                          partition_name =>p_partition
  59.                          );
  60.      dbms_output.put_line('unformatted_blocks :'||v_unformatted_blocks);
  61.      dbms_output.put_line('fs1_blocks (0-25):'|| v_fs1_blocks);
  62.      dbms_output.put_line('fs2_blocks (25-50):'|| v_fs2_blocks);
  63.      dbms_output.put_line('fs3_blocks (50-75):'|| v_fs3_blocks);
  64.      dbms_output.put_line('fs4_blocks (75-100):'|| v_fs4_blocks);
  65.      dbms_output.put_line('full_blocks :'||v_full_blocks);
  66.    else
  67.      --dbms_space.free_blocks
  68.      dbms_space.free_blocks(segment_owner =>p_owner,
  69.                          segment_name =>p_seg_name,
  70.                          segment_type =>p_type,
  71.                          freelist_group_id =>0,
  72.                          free_blks =>v_free_blocks,
  73.                          partition_name =>p_partition
  74.                          );
  75.      dbms_output.put_line('free_blks:'||v_free_blocks);
  76.    end if;
  77.    --dbms_space.unused_space
  78.    dbms_space.unused_space(segment_owner =>p_owner,
  79.                          segment_name =>p_seg_name,
  80.                          segment_type =>p_type,
  81.                          total_blocks =>v_total_blocks,
  82.                          total_bytes =>v_total_bytes,
  83.                          unused_blocks =>v_unused_blocks,
  84.                          unused_bytes =>v_unused_bytes,
  85.                          last_used_extent_file_id =>v_last_used_ext_file_id,
  86.                          last_used_extent_block_id =>v_last_used_ext_block_id,
  87.                          last_used_block =>v_last_used_block,
  88.                          partition_name =>p_partition
  89.                          );
  90.     dbms_output.put_line('total_blocks :'||v_total_blocks);
  91.     dbms_output.put_line('total_bytes :'||v_total_bytes);
  92.     dbms_output.put_line('unused_blocks :'||v_unused_blocks);
  93.     dbms_output.put_line('unused_bytes :'||v_unused_bytes);
  94.     dbms_output.put_line('last_used_extent_file_id :'||v_last_used_ext_file_id);
  95.     dbms_output.put_line('last_used_extent_block_id :'||v_last_used_ext_block_id);
  96.     dbms_output.put_line('last_used_block :'||v_last_used_block);
  97. end;



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2116162/，如需轉載，請註明出處，否則將追究法律責任。

相關文章