Re: Table Partition Count

From: matthew <matthiasrogel_at_googlemail.com>
Date: Fri, 27 Nov 2009 03:59:35 -0800 (PST)
Message-ID: <a14b43ff-35a8-474b-958a-1f052410117f_at_x31g2000yqx.googlegroups.com>

create function count_(vtabname in varchar2) return sys.odcivarchar2list pipelined
is

   s long;
   cu sys_refcursor;
   r varchar2(256);
begin

   for su in
   (

      select table_name, partition_name, subpartition_name
      from user_tab_subpartitions
      where table_name = vtabname
   ) loop
      s :=  'select ''Table "' || su.table_name || '", Partition "' ||

su.partition_name || '", Subpartition "' || su.subpartition_name || '": count '' || count(*) from "' ||

           dbms_assert.sql_object_name(vtabname) || '" subpartition("' || su.subpartition_name || '")';

      open cu for s;
      loop
         fetch cu into r;
         exit when cu%notfound;
         pipe row(r);
      end loop;
      close cu;

   end loop;

   for p in
   (

      select table_name, partition_name
      from user_tab_partitions
      where table_name = vtabname
   ) loop
      s :=  'select ''Table "' || p.table_name || '", Partition "' ||
p.partition_name || '": count  '' || count(*) from "' ||
           dbms_assert.sql_object_name(vtabname) || '" partition("' ||
p.partition_name || '")';
      open cu for s;
      loop
         fetch cu into r;
         exit when cu%notfound;
         pipe row(r);
      end loop;
      close cu;

   end loop;

   return;
end;
/

select * from table(count_('T1'))
/
Received on Fri Nov 27 2009 - 05:59:35 CST