ORACLE10g ASSM 学习(一)

创建存储过程show_space_assm

 

CREATE OR REPLACE PROCEDURE show_space_assm(p_segname_1 IN VARCHAR2,
                                            p_owner_1   IN VARCHAR2 DEFAULT USER,
                                            p_type_1    IN VARCHAR2 DEFAULT 'TABLE') AS

  p_segname VARCHAR2(100);
  p_owner   VARCHAR2(30);
  p_type    VARCHAR2(10);

  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_fulv_blocks        NUMBER;
  v_fulv_bytes         NUMBER;
  v_unformatted_blocks NUMBER;
  v_unformatted_bytes  NUMBER;

  PROCEDURE p(p_label IN VARCHAR2, p_num IN NUMBER) IS
  BEGIN
    dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
  END;

BEGIN
  p_segname := upper(p_segname_1);
  p_owner   := upper(p_owner_1);
  p_type    := p_type_1;

  IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN
    --rainy changed
    p_type := 'INDEX';
  END IF;

  IF (p_type_1 = 't' OR p_type_1 = 'T') THEN
    --rainy changed
    p_type := 'TABLE';
  END IF;

  IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN
    --rainy changed
    p_type := 'CLUSTER';
  END IF;

  dbms_space.space_usage(segment_owner      => p_owner
                        ,segment_name       => p_segname
                        ,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_fulv_blocks
                        ,full_bytes         => v_fulv_bytes);

  dbms_output.put_line(rpad(' ', 50, '*'));

  p('0% -- 25% free space blocks', v_fs1_blocks);
  p('0% -- 25% free space bytes', v_fs1_bytes);
  p('25% -- 50% free space blocks', v_fs2_blocks);
  p('25% -- 50% free space bytes', v_fs2_bytes);
  p('50% -- 75% free space blocks', v_fs3_blocks);
  p('50% -- 75% free space bytes', v_fs3_bytes);
  p('75% -- 100% free space blocks', v_fs4_blocks);
  p('75% -- 100% free space bytes', v_fs4_bytes);
  p('Unused Blocks', v_unformatted_blocks);
  p('Unused Bytes', v_unformatted_bytes);
  p('Full Blocks', v_fulv_blocks);
  p('Full bytes', v_fulv_bytes);

END;

 

 

SQL> set serveroutput on;
SQL> exec show_space_assm('workinfo','factory');

 

效果 
*************************************************
0% -- 25% free space blocks.............1
0% -- 25% free space bytes..............8192
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Full Blocks.............................4
Full bytes..............................32768