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

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;
/