SQL Script Count Partition Row Counts

Updated : 27-March-2002
Version : 2.0

Description

Displays each individual table partition row counts.

Parameters

&1 - Table Owner

SQL Source

set serverout on size 1000000
set verify off

spool numrowspart_&&1..lst

declare

sql_stmt varchar2(1024);

row_count number;

cursor get_tab is
      select table_name,partition_name
      from dba_tab_partitions
      where table_owner=upper('&&1');

begin

dbms_output.put_line('Checking Record Counts for schema &&1 ');
dbms_output.put_line('Log file to numrows_part_&&1.lst ....');
dbms_output.put_line('....');

for get_tab_rec in get_tab loop

BEGIN
sql_stmt := 'select count(*) from &&1..'||get_tab_rec.table_name
                   ||' partition ( '||get_tab_rec.partition_name||' )'; 

EXECUTE IMMEDIATE sql_stmt INTO row_count;

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name
                            ||'('||get_tab_rec.partition_name||')',50)
                             ||' '||TO_CHAR(row_count)||' rows.');

exception when others then
     dbms_output.put_line
           ('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

end;
/
set verify on
spool off

Previous Oracle Version Links

Count Partition Row Counts

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal