SQL Script

Count Partition Rows

Updated: 27-Sep-2002
Version: 2.1

Description

Displays each individual table partition row counts.

Also now lists the number of rows from an ANALYZE TABLE or GATHER STATISTICS command for comparison. It they are vastly different, analyze the table partition again.

Parameters

1 - Table Owner

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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, num_rows
      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||')',40)
                             ||' '||TO_CHAR(row_count)||' rows (Analyze '||get_tab_rec.num_rows||' 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

Site Map

SQL Script

Count Partition Rows

Updated: 27-Sep-2002Version: 2.1

Description

Parameters

SQL Source

Related Links

Site Map

Updated: 27-Sep-2002
Version: 2.1