파티션 인덱스 세그먼트 확인 스크립트

set serverout on size 1000000

DECLARE

  v_owner varchar2(30);

  v_tab_name varchar2(30);



  cursor part_tab is

  select a.owner, a.table_name, a.partitioning_type, a.partition_count, b.column_name, b.column_position

  from dba_part_tables a,

       dba_part_key_columns b

  where a.owner not in ('SYS','SYSTEM')

  and  a.owner = b.owner

  and  a.table_name = b.name

  order by a.owner, a.table_name, b.column_position;



  cursor part_ind is

  select /*+ FIRST_ROWS */

         sum(decode(di.PARTITIONED,'NO',decode(di.UNIQUENESS,'UNIQUE',1,0),0)) NP_U,

         sum(decode(di.PARTITIONED,'NO',decode(di.UNIQUENESS,'NONUNIQUE',1,0),0)) NP_NU,

         sum(decode(di.PARTITIONED,'YES',

                decode(dpi.locality,'GLOBAL',decode(di.UNIQUENESS,'UNIQUE',1,0),0),0)) GP_U,

         sum(decode(di.PARTITIONED,'YES',

                decode(dpi.locality,'GLOBAL',decode(di.UNIQUENESS,'NONUNIQUE',1,0),0),0)) GP_NU,

         sum(decode(di.PARTITIONED,'YES',

                decode(dpi.locality,'LOCAL',decode(dpi.alignment,'PREFIXED',

                      decode(di.UNIQUENESS,'UNIQUE',1,0),0),0),0)) LP_U,

         sum(decode(di.PARTITIONED,'YES',

                decode(dpi.locality,'LOCAL',decode(dpi.alignment,'PREFIXED',

                      decode(di.UNIQUENESS,'NONUNIQUE',1,0),0),0),0)) LP_NU,

         sum(decode(di.PARTITIONED,'YES',

                decode(dpi.locality,'LOCAL',decode(dpi.alignment,'NON_PREFIXED',

                      decode(di.UNIQUENESS,'UNIQUE',1,0),0),0),0)) LNP_U,

         sum(decode(di.PARTITIONED,'YES',

                decode(dpi.locality,'LOCAL',decode(dpi.alignment,'NON_PREFIXED',

                      decode(di.UNIQUENESS,'NONUNIQUE',1,0),0),0),0)) LNP_NU



  from dba_indexes di,

       dba_part_indexes dpi

  where di.owner = v_owner

  and   di.table_name = v_tab_name

  and   di.owner = dpi.owner (+)

  and   di.index_name = dpi.index_name (+);



  r_ptab part_tab%rowtype;

  r_pind part_ind%rowtype;



BEGIN



  open part_tab;

  loop

    fetch part_tab into r_ptab;

    exit when part_tab%notfound;



    v_owner := r_ptab.owner;

    v_tab_name := r_ptab.table_name;



      open part_ind;

      loop

        fetch part_ind into r_pind;

        exit when part_ind%notfound;



        dbms_output.put_line(r_ptab.owner||' '||r_ptab.table_name||' '||r_ptab.partitioning_type||' '

            ||to_char(r_ptab.partition_count)||' '||r_ptab.column_name||' '||to_char(r_ptab.column_position)||' '

            ||to_char(r_pind.NP_U)||' '||to_char(r_pind.NP_NU)||' '||to_char(r_pind.GP_U)||' '

            ||to_char(r_pind.GP_NU)||' '||to_char(r_pind.LP_U)||' '||to_char(r_pind.LP_NU)||' '

            ||to_char(r_pind.LNP_U)||' '||to_char(r_pind.LNP_NU));

      end loop;

      close part_ind;



  end loop;

  close part_tab;

END;

/

set serverout on size 1000000



DECLARE

  v_owner varchar2(30);

  v_tab_name varchar2(30);



  cursor part_tab is

  select a.owner, a.table_name, a.partitioning_type, a.partition_count

  from dba_part_tables a

  where a.owner not in ('SYS','SYSTEM')

  order by a.owner, a.table_name;



  r_ptab part_tab%rowtype;

  v_sum_s number;

  v_avg_s number;

  v_min_s number;

  v_max_s number;

  v_avg_r number;

  v_min_r number;

  v_max_r number;



BEGIN



  open part_tab;

  loop

    fetch part_tab into r_ptab;

    exit when part_tab%notfound;



    v_owner := r_ptab.owner;

    v_tab_name := r_ptab.table_name;



    select round(sum(bytes)/1024/1024) sum_s,

           round(avg(bytes)/1024/1024) avg_s,

           round(min(bytes)/1024/1024) min_s,

           round(max(bytes)/1024/1024) max_s

    into   v_sum_s, v_avg_s, v_min_s, v_max_s

    from   dba_segments

    where  owner = r_ptab.owner

    and    segment_name = r_ptab.table_name;



    select round(avg(num_rows)) avg_r,

           min(num_rows) min_r,

           max(num_rows) max_r

    into   v_avg_r, v_min_r, v_max_r

    from   dba_tab_partitions

    where  table_owner = r_ptab.owner

    and    table_name = r_ptab.table_name;





    dbms_output.put_line(r_ptab.owner||' '||r_ptab.table_name||' '

        ||to_char(v_sum_s)||' '||to_char(v_avg_s)||' '||to_char(v_min_s)||' '

        ||to_char(v_max_s)||' '||to_char(v_avg_r)||' '||to_char(v_min_r)||' '||to_char(v_max_r));



  end loop;

  close part_tab;

END;

/